Lookup but filtering on active records only

(0) Share

Report

Posted on by dgarrick

Hi All,

I looking for some help as I can't get a forumla together without an error.

I have three tables

Helpdesk - It holds details of fault tickets.

Engineer Assignments - A table that holds which engineers have been assigned to a helpdesk ticket.

Engineers - Holds the name a details of the engineer

Each table is has a CDS lookup, Helpdesk to Engineer Assigment and Engineer Assigment to Engineers

I have a canvass app that I've created that has a gallery that shows Open Helpdesk tickets.

I can retrieve the Engineers Name from my table but only the first record, even if its inactive.

What I want to be able to do is change the formula below to either show Status=Active records from Engineer Assignments or show the last record from Engineer Assignment which would also be the active record, but I still need to retrieve the engineer name. I just don't seem to be able to cover both objectives.

LookUp('Engineer Assignments',Ticket.Helpdesk= ThisItem.Helpdesk, Engineer.Name)

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (18)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dgarrick It's possible to do what u want. But could you please share a little more about the fields you have in each of the tables (particularly the lookups). Could u also clarify your 2 objectives a more.

Was this reply helpful? Yes No
dgarrick 24 on at

Like (0)

Report
Copy link

Link copied!

Hi @CNT thanks for responding.

Objective is to have a gallery show the following fields

Ticket No, Client, Site from the Helpdesk Table then the Engineers Name. I've attached a screenshot of some data tables showing the GUID fields. I've got the lookup to retrieve the name using the formula below, but I want to firther restrict it to only look at the Engineer Assignments table, Status Active, before it does the lookup of the engineers name.

LookUp('Engineer Assignments',Ticket.Helpdesk= ThisItem.Helpdesk, Engineer.Name)

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@dgarrick Try this formula. Here I'm assuming that Status is a Text Field and that there is only one "Active" Assignment for each Ticket. If this is not true, well have to modify this slightly. Please let me know.
LookUp('Engineer Assignments',Ticket.Helpdesk= ThisItem.Helpdesk && Status="Active").Engineer.Name

Was this reply helpful? Yes No
dgarrick 24 on at

Like (0)

Report
Copy link

Link copied!

Hi @CNT
Looking at the lookup you've provided it looks like your saying if the helpdesk ticket is active, lookup the engineers name.
The lookup I provided looks up the engineers name using the GUID from Engineers Assignments table, but I need it to only look at active status records from the engineers asssignments table.
The engineer assignments status does not appear in the heldesk data table.

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dgarrick If I understand it right, you have a gallery with datasource, Helpdesk table.
For any selected item in the gallery there could be many records in the Engineers Assignments table but you want you want to find the "Active" one. Then further find the Engineer assigned to this single "Active" Assignment.
Is this right?

Was this reply helpful? Yes No
dgarrick 24 on at

Like (0)

Report
Copy link

Link copied!

@CNT , Thats exactly right thats what I want to acheive.

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@dgarrick Please try this formula that I had given earlier and let me know if it gives the results you want (make sure the Table, column names match your schema).
LookUp('Engineer Assignments',Ticket.Helpdesk= ThisItem.Helpdesk && Status="Active").Engineer.Name

Was this reply helpful? Yes No
dgarrick 24 on at

Like (0)

Report
Copy link

Link copied!

That didnt work, showing a red marker under the equals before the status part in the lookup.

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dgarrick I believe the Error is because the Status is not a String. I don't know that is the data type unless you tell me.

Was this reply helpful? Yes No
dgarrick 24 on at

Like (0)

Report
Copy link

Link copied!

I looking at the inbuilt status where you can choose as active or inactive.
Screen grab of the table structure for status is below.

Was this reply helpful? Yes No