How to use Lookup when using SPS Lookup Columns?

Question

When I have two tables (Customer and Order) and Order has an SPS Lookup column to link to customer I can use the Lookup function in PowerApps to display customer information in a Gallery of Orders.

For example: Assuming ThisItem is an Order: Lookup(Customer, ID = ThisItem.Customer_Id, 'Company Name') works just fine.

But with a One-to-Many relationship, using SPS Lists to store the relationships, I cannot get the nested lookup to work.

For Example, with the addition of a Service list and a Ordered_Service list, I can link an unlimited number of services to an Order, by storing order_id and service_id in my SPS list using lookup columns.

But when I attempt to do lookup the ServiceName from an Order Gallery, I am unsuccessful

Lookup(Service, ID = Lookup(
      ordered_service,
      order_id = ThisItem.ID, service_id),
  Service_Name)

I've tried using both the order_id.Id and order_id.Value and wrapping both sides of my condition in Text() and always get 2 Invalid Type errors and a Delegation Warning.

I'd like to know how to properly do this Lookup and I can modify the List relationships if needed.

NOTE: I assume using SQL this would likely work better, but its not an option.

Thanks!

Patrick Patrick · Accepted Answer · 2019-05-10T21:57:38

Took a break. Worked out. Sat back down. figured it out.
^^ That should be a standard answer for all questions. ;-)

You have to wrap ALL the things in Text() AND use .Value on all not .ID (that is primary Key) fields.

Lookup(Service, Text(ID) = Text(Lookup(
    ordered_service,
    Text(order_id.Value) = Text(ThisItem.ID),
    service_id.Value)),
  Service_Name)

How to use Lookup when using SPS Lookup Columns?

1 Answers