cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nzblue_fish
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
GarethPrisk
Resident Rockstar
Resident Rockstar

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,668)