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
)
)
Solved! Go to Solution.
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
)
)
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
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)
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
)
)
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).
User | Count |
---|---|
258 | |
111 | |
95 | |
48 | |
41 |