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

Disambiguation on Lookup Columns

Hi

 

Tried to do a search for this without joy, so sorry if this is a stupid question.

 

I have three data sets, they are Sharepoint Lists, let's call them Orders, Products and Items.

 

Orders and Items both have columns that lookup to the Product. In both of the lists, these lookup columns are called "Product".

 

In my app, I have a combo box to allow users to choose an Record from 'Items'. I am trying to filter this combobox so that only 'Items' linked to 'Products' which have 'Orders' linked to them appear..... So far so contrived....

 

Here's where I've messed up by giving columns the same names...

 

In the Items field in the combobox I am trying to do the following, but I can't workout how to make the disambiguation work properly:


Filter(Items.ItemID,

   Sum(

      Filter('Orders',Product.Id = 'Items'[@Product.Id])

      ,'Order Quantity')>0

)

 

Without the disambiguation, it returns every row (since Orders.Product.Id always equals Orders.Product.Id). With the disambiguation I get an error telling me the name I've chosen isn't valid.

 

So the question is this:

How do you disambiguate a lookup field?

 

------------------------------------------------------------------------

PS. I know that the Sum of the order quantity doesn't make a huge amount of sense in this context - it's because I've simplified what I doing for demonstrative purposes.

In real life I am summing Ordered Quantity and a Shipped Quantity. I only want Items to appear if the Quantity of Orders for the Product the Item is identified as exceeds the Quantity of Items of that Product that have been Shipped.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

I've achieved what I was trying to do (albeit in a different way)

I assume my issues was something to do with Scope, my innermost filter was seemingly unable to access my outermost. 

To avoid nesting filters I used AddColumns to add dynamic Ordered and Shipped columns to my Items List and used these in the condition of my filter.

 

Filter(
    AddColumns('Items',"Ordered",
        Sum(
            Filter('Orders','Orders'[@Product].Id = 'Items'[@Product].Id)
            ,'Order Quantity' ///Total Order Quantity
        ),
        "Shipped",
        Sum(
            Filter('Shipments','Shipments'[@Product].Id = 'Items'[@Product].Id)
            ,Quantity) /// Total Shipping Quantity
        ),
    Ordered>Shipped)

 

View solution in original post

5 REPLIES 5
Super User III
Super User III

@SeamusHarkins1 

You might consider using the As operator.

Filter(Items.ItemID As _items,

   Sum(

      Filter('Orders',Product.Id = _Items.Product.Id)

      ,'Order Quantity')>0

)

 

This helps with the disambiguation and also makes it a little more readable. 

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Thanks for taking a look @RandyHayes 

 

I get an error in the formula bar when I try this. _items.Product.Id isn't valid / recognized, even after I use the As operator

Super User III
Super User III

@SeamusHarkins1 

You might need to share your formula then so I can review it as this solution definitely works well.  If it is not in your situation, then there must be something else afoot. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Hi @RandyHayes 

 

Here's the formula  - It doesn't seem to like me using _item.Product.Id in the filter, but using other columns from the Items table (such as _Item.'Item ID') does not present an error.

 

Filter(Items.'Item ID' As _item,
Coalesce(Sum(Filter('Orders',Product.Id = _item.Product.Id),'Order Quantity'),0) ///Ordered
>
Coalesce(Sum(Filter('Shipments',Product.Id = _item.Product.Id),Quantity),0)) ///Shipped

Frequent Visitor

I've achieved what I was trying to do (albeit in a different way)

I assume my issues was something to do with Scope, my innermost filter was seemingly unable to access my outermost. 

To avoid nesting filters I used AddColumns to add dynamic Ordered and Shipped columns to my Items List and used these in the condition of my filter.

 

Filter(
    AddColumns('Items',"Ordered",
        Sum(
            Filter('Orders','Orders'[@Product].Id = 'Items'[@Product].Id)
            ,'Order Quantity' ///Total Order Quantity
        ),
        "Shipped",
        Sum(
            Filter('Shipments','Shipments'[@Product].Id = 'Items'[@Product].Id)
            ,Quantity) /// Total Shipping Quantity
        ),
    Ordered>Shipped)

 

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (44,400)