Adding a column to a record variable

[ElectricLlama2]

I'm loading a global record variable with this:

Set(
    rCurrentWeek,
    LookUp(

        'dbo.Week',
        Today() >= DateWeekStarting && Today() <= DateWeekEnding
    )
)

There are lots of useful columns in this record but there is no "prior week" column.

Currently I work out the prior week and put it in a seperate global variable but I'd much rather include it as another property inside this record.

Is there a way to add a property/column to an existing record? There are examples for collections everywhere but I can't find the syntax to add to a record. These attempts return various errors:

A. Try to add to existing record

    ;Set(

        rTest,

        AddColumns(

            rCurrentWeek, // already populated this record with Set

            "LastRetailWeekLastYear",

            gLastWeekLastYear // already worked this out

            )

    )

B. Try to add inline while loading record

;Set(
    rCurrentWeek,
      AddColumns(
        LookUp(
          'dbo.Weeks',
          Today() >= DateWeekStarting && Today() <= DateWeekEnding
        ),
    "LastWeekLastYear",
    0 // as an example
)
)

[Alex_10]

@ElectricLlama2 

you cannot use AddColumns with the datatype of Record.

It will work if your variable is a Table, not a record (LookUp returns a Record, not a Table).

for example:

Set(
    rCurrentWeek,
      AddColumns(
        Table(LookUp(
          'dbo.Weeks',
          Today() >= DateWeekStarting && Today() <= DateWeekEnding
        )),
    "LastWeekLastYear",
    0 // as an example
)
)

In this case there is no big difference with a collection:

if you want to get a value stored in that variable, you need always use First(rCurrentWeek).columnName

[ElectricLlama2]

Thanks for your response, so in short, there is no way to add a column to an existing record.

It seems like a workaround would be to create a brand new record and assign the columns one by one and clear the old one:

Set(
    rCurrentWeekTemp,
    LookUp(

        'dbo.Week',
        Today() >= DateWeekStarting && Today() <= DateWeekEnding
    )
)

;Set(
    rCurrentWeek,
    {
        WeekKey: rCurrentWeekTemp.WeekKey,
        YearKey: rCurrentWeekTemp.YearKey,
        PriorWeek: AVariableFromSomewhereElse
    }
)

;Clear(rCurrentWeekTemp)

[Alex_10 · Accepted solution]

@ElectricLlama2 

My response was regarding your code B.

you can add a column to the variable (based on your code A):

Set( rCurrentWeek,
    LookUp(
      AddColumns( 'dbo.Weeks', "LastWeekLastYear", 0),
      Today() >= DateWeekStarting && Today() <= DateWeekEnding
    )
)

[JeffC1]

Wrap the Record with Table(), then AddColumns(), and finally wrap all of it with First():

For example, normally you may have created a record variable containing the user's complete office365 profile like this:

Set(
    varOfficeProfile,
    Office365Users.MyProfileV2()
)

But if you wanted to add columns to this record variable

Set(
    varOfficeProfile,
    First(
        AddColumns(
            Table(Office365Users.MyProfileV2()),
            "FullName",
            User().FullName,
            "Photo",
            User().Image
        )
    )
)

 So in the example you provided, it would be done this way:

Set(
    varCurrentWeek,
      First(
          AddColumns(
              Table(
                  LookUp('dbo.Weeks', Today() >= DateWeekStarting && Today() <= DateWeekEnding)
                  ),
                  "LastWeekLastYear",
                  0
                  )
            )
)

I bring this up as a solution even though one has already been presented because the original solution would not work in all situations (like my Office365 example).

[pp365]

Many thanks @JeffC1  for the extremely clearly explained solution to this - really helpful and worked in my case. Have 'Liked' your post.