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

Lookup in AddColumn Syntax based on Filter Recordset

Hi All,

 

I am having an issue trying to get the syntax right for an AddColumn entry that uses a Lookup during a collection build.

 

Below is the syntax I'm trying. I'm sure this is a disambiguation issue, but I can't see how to reference the record from the Filter during the Lookup function.  The common key in the two recordsets is billableItemID.  The existing collection colBillableInventory has the value to be inserted as column preferredSeq as the new collection colJobBillableItems is being populated.

 

Any help would be much appreciated. Cheers, Innes

 

 

ClearCollect(
    colJobBillableItems,
    AddColumns(
        Filter(
            Job_Billable_Items,
            Title = galJobList.Selected.'ULS Job ID'
        ),
        "ItemDesc",
        billableItemID,
        "preferredSeq",
        LookUp(colBillableInventory,billableItemID = ThisRecord.billableItemID,InvoiceSeqOrdering),
        "isDeletedItem",
        false,
        "isChangedItem",
        false
    )
);

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @nzblue_fish :

This seems to be a disambiguation problem.Generally, when encountering this kind of problem, I will give priority to renaming the ambiguous field name.Please try this code:

ClearCollect(
    colJobBillableItems,
    AddColumns(
        Filter(
            Job_Billable_Items,
            Title = galJobList.Selected.'ULS Job ID'
        ),
        "ItemDesc",
        billableItemID,
        "preferredSeq",
        LookUp(RenameColumns(colBillableInventory,"billableItemID","TheID"),TheID = billableItemID,InvoiceSeqOrdering),
        "isDeletedItem",
        false,
        "isChangedItem",
        false
    )
);

Here is my test:

My data source:

ClearCollect(
    Table1,
    {billableItemID: 1},
    {billableItemID: 2}
);
ClearCollect(
    colBillableInventory,
    {
        billableItemID: 1,
        InvoiceSeqOrdering: "NO1"
    },
    {
        billableItemID: 2,
        InvoiceSeqOrdering: "NO2"
    }
)

Table1:

 AddColumns(Table1, "ItemDesc",
        billableItemID, "preferredSeq",
        LookUp(RenameColumns(colBillableInventory,"billableItemID","TheID"),TheID = billableItemID,InvoiceSeqOrdering))

2.JPG

Table2:

 AddColumns(Table1, "ItemDesc",
        billableItemID, "preferredSeq",
        LookUp(colBillableInventory,colBillableInventory[@billableItemID] = billableItemID,InvoiceSeqOrdering))

3.JPG

Best Regards,

Bof

 

 

View solution in original post

Thanks for the suggested solution.  Based on your suggestion I got the Lookup working within the AddColumns, although I ended up creating a small temporary collection with just the key field and the value I needed during the lookup.   Aside from getting this working, which I greatly appreciate, I have also learnt that you can rename columns.

 

Cheers, and thanks for your help.

View solution in original post

5 REPLIES 5
Super User II
Super User II

I think you're correct that it's just a disambiguation issue. Also, I don't know that ThisRecord works in contexts outside of ForAll, Sum, or With. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators

 

Instead, you'll have the exact context of your billableItemID column (you used it explicitly in another column you added), and then just disambiguate the use of the collection with Table[@column] or colBillableInventory[@billableItemID]. If this data types do indeed match, then this should resolve.

 

ClearCollect(
    colJobBillableItems,
    AddColumns(
        Filter(
            Job_Billable_Items,
            Title = galJobList.Selected.'ULS Job ID'
        ),
        "ItemDesc",
        billableItemID,
        "preferredSeq",
        LookUp(colBillableInventory,colBillableInventory[@billableItemID] = billableItemID,InvoiceSeqOrdering),
        "isDeletedItem",
        false,
        "isChangedItem",
        false
    )
);

 

Hi and thanks for the reply.  I had already tried that syntax, in fact it was my first attempt, but it did not behave in the way expected so I tried some alternatives.

 

Using the syntax as you've specified doesn't throw an error but it sets the value of "preferredSeq" to the value of "invoiceSeqOrdering" of the first record in the colBillableInventory collection.  This means each entry in the colJobBillableItems is set the same as record 1.  If I do a Lookup for a label field in the gallery displaying the colJobBillableItems then I get the correct value of "preferredSeq" for the matching key, whereas the value set in the ClearCollect Lookup retrieves only the value from the first entry in the related collection.  Below you'll see column 2 shows the "invoiceSeqOrdering" from record 1 of colBillableInventory and column 3 what it should be for the key value given in column 4.  Hope that's not too confusing.  Cheers, Innes

 

nzblue_fish_0-1600679652108.png

 

Hi @nzblue_fish :

This seems to be a disambiguation problem.Generally, when encountering this kind of problem, I will give priority to renaming the ambiguous field name.Please try this code:

ClearCollect(
    colJobBillableItems,
    AddColumns(
        Filter(
            Job_Billable_Items,
            Title = galJobList.Selected.'ULS Job ID'
        ),
        "ItemDesc",
        billableItemID,
        "preferredSeq",
        LookUp(RenameColumns(colBillableInventory,"billableItemID","TheID"),TheID = billableItemID,InvoiceSeqOrdering),
        "isDeletedItem",
        false,
        "isChangedItem",
        false
    )
);

Here is my test:

My data source:

ClearCollect(
    Table1,
    {billableItemID: 1},
    {billableItemID: 2}
);
ClearCollect(
    colBillableInventory,
    {
        billableItemID: 1,
        InvoiceSeqOrdering: "NO1"
    },
    {
        billableItemID: 2,
        InvoiceSeqOrdering: "NO2"
    }
)

Table1:

 AddColumns(Table1, "ItemDesc",
        billableItemID, "preferredSeq",
        LookUp(RenameColumns(colBillableInventory,"billableItemID","TheID"),TheID = billableItemID,InvoiceSeqOrdering))

2.JPG

Table2:

 AddColumns(Table1, "ItemDesc",
        billableItemID, "preferredSeq",
        LookUp(colBillableInventory,colBillableInventory[@billableItemID] = billableItemID,InvoiceSeqOrdering))

3.JPG

Best Regards,

Bof

 

 

View solution in original post

Thanks for the suggested solution.  Based on your suggestion I got the Lookup working within the AddColumns, although I ended up creating a small temporary collection with just the key field and the value I needed during the lookup.   Aside from getting this working, which I greatly appreciate, I have also learnt that you can rename columns.

 

Cheers, and thanks for your help.

View solution in original post

Hi @nzblue_fish :

Glad to know that your problem has been solved.

If my post helps, then please consider Accept it as the solution to help others.Thanks.

Best Regards,

Bof

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (7,144)