cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElectricLlama2
Frequent Visitor

Adding a column to a record variable

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
)
)

1 ACCEPTED SOLUTION

Accepted Solutions
Alex_10
Super User
Super User

@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
    )
)

 

 

View solution in original post

4 REPLIES 4
Alex_10
Super User
Super User

@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
Frequent Visitor

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
Super User
Super User

@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
Frequent Visitor

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).

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,145)