Is it possible to access data from a LookUp within a Lookup?

[Anonymous]

Bit of a confusing question here.....I know. Let me explain my scenario as I'm currently stumped on how to access the specific data I am looking for. 

**PLEASE LOOK AT IMAGE ATTACHMENT FOR REFERENCE**

This Assignments app filters Data to only show technician assignments that are associated with a specific technician, utilizing their AzureAD email. 

Filter('FCO Assignments', _technicianemail=ThisUser.altEmail)

However, some of the missing fields within the Gallery come from entity relationships that pertain to Work Orders, and is dynamic based on the workorderID that is associated with that Gallery item. 

I have 3 levels of Entity relationships - (FCOAssignments --> FCOWorkOrders --> FCOCustomerSites)

In this App, I'm trying to access the 'sitename' of the FCOCustomerSite LookUp Field within the Work Order(FCOWorkOrder) Entity. 

The code I'm trying to use to access this data is :

LookUp('FCO Work Orders', _workorderguid=ThisItem._workorderid)._FCOCustomerSite._sitename

which searches for the correct WO based on the workorderguid and return the sitename from the CustomerSite LookUp Record that's associated with that WO. 

This LookUp function has no errors, but none of the correct data comes through. 

So my question is.......Is this even possible? Can I transcend that many levels and still receive data?

I've tried to think of other ways that I could potentially create a collection on start with the necessary data, but nothing has panned out due to the dynamic nature of the workorderID's from the Gallery.

Any help is greatly appreciated!

[tchin-nin]

Hi @Anonymous,

I would probably Add 2 columns for the user assignments : 1 containing the associated WorkOrder item and 1 containing the CustomerSite associated.

ClearCollect(UserAssignments,AddColumns(AddColumns(Filter('FCO Assignments', _technicianemail=ThisUser.altEmail),"WorkOrder",LookUp('FCO Work Orders', _workorderguid=_workorderid)),"CustomerSite",LookUp('FCO Customer Sites',_customersiteguid=WorkOrder._customersiteid)))

At the end, your UserAssignments collection has all the Assignment properties, and 2 more :

WorkOrder that contains the whole associated WorkOrder item

CustomerSite that contains the whole associated CustomerSite item
So you can use the collection UserAssignments as your gallery's Items property and use WorkOder.PropertyName to reference any information about the work order, and CustomerSite.PropertyName to reference the customer site.

Why do I store the whole Item ? So you can easily reference several properties of the WorkOder or CustomerSite item without having to lookup every time. Is you just need the name you can use : LookUp(table, condition, result) the result parameter is the table's column you want to return.

Théo

[Anonymous]

@tchin-nin Awesome, TYVM Theo. Let me play around with this idea and see if I can get this to work. 

I was thinking I could potentially head down a road like this, but my only concern was having to save the Site ID in addition to the 'FCOCustomerSite' Record within the Work Order Entity, which somewhat produces redundant data. 

I would also have to replicate this process for a few other scenarios that are similar within the Work Order Entity. 

[tchin-nin]

Hi

If your WorkOrder entity have links to other tables you can add columns to these entities within the same AddColumns() function as the CustomerSite entity : 

AddColumns(DataSource, "Column1", expression1, "Column2", expression2, ...)

The reason why I have to use 2 nested AddColumns() functions here is because you can not reference columns added within the same AddColumns function. To have the CustomerSite ID, I need to reference the WorkOrder entity.
Let say you have this :

Assignments
id_assignments, assignmentInfo1, assignmentInfo2, fk_id_workorder
WorkOrder
id_workorder, workorderinfo1, fk_id_customersite, fk_id_otherentity1, fk_id_otherentity2

CustomerSite

id_customersite, customersiteInfo1

OtherEntity1

id_otherentity1, otherentity1Info1

OtherEntity2

id_otherentity2, otherentity2Info1

Then you can access all your info with 2 AddColumns functions :

AddColumns(

    AddColumns(

        Assignemnts,

        "WorkOrder", Lookup(WorkOrder,fk_id_workorder=id_workorder)),

    "CustomerSite", Lookup(CustomerSite,WorkOrder.fk_id_customersite=id_customersite),

    "OtherEntity1", Lookup(OtherEntity1,WorkOrder.fk_otherentity1=id_otherentity1),

    "OtherEntity2", Lookup(OtherEntity2,WorkOrder.fk_otherentity2=id_otherentity2))

Then in your gallery you can reference all infos:

ThisItem.assignemntInfo1

ThisItem.WorkOrder.workorderinfo1

ThisItem.CustomerSite.customersiteInfo1

ThisItem.OtherEntity1.otherentity1Info1

ThisItem.OtherEntity2.otherentity2Info1

Hope it helps understanding how AddColumns() works so you can adjust to what you need.

Théo

[Anonymous · Accepted solution]

So our team was able to find a quick workaround to this issue. 

Rather than using the Lookup function, we filtered the initial Work Order Data that matches the GUID criteria, and wrapped that inside a First() function. This allowed the 1 record to be returned that meets the conditional criteria, populated the data correctly. 

First(Filter('FCO Work Orders', cr4c8_workorderguid=ThisItem.cr4c8_workorderid).cr4c8_FCOCustomerSite).cr4c8_sitename

In the long run I don't know how this will affect performance, but we will have to cross that bridge at a later time. 

Thank you all for the suggestions and help. 

- Matt