How to filter a lookup field in dropdown then update form from this lookup field?

(0) Share

Report

Posted on by mokhawaja

239

Hi,

I am building an app for my medical reps who visit doctors on certain pharmaceutical products so every medical rep will be assigned to a certain product, and a certain number of doctors, and the medical rep should record his calls to doctors on daily basis
so what I used to have in my Access Web app the following tables:
1. Med reps: containing Med rep name, and other detail fields
2. Doctors: containing Doctor name, and other detail fields.
3. daily calls (Daily Reports): here the med rep records his calls so he/she enter his/her name (lookup to med rep table), date of the call, and other details of the call and most importantly name of the doctor (lookup to doctors table).
4. MedRepDoctorList: which contains two lookup fields: Med rep name (lookup to the med reps table) and doctor name (lookup to Doctors table)... and this table is used mainly for validation... e.g.: if med rep "A" is assigned to visit only Doctor "Y" and "X", then A will not be able to add a call record of visiting Doctor "Z", who is not included in his assigned doctor list. (I was able to do this using macros in Access).
now I am building my powerapp on CDS and moving my data into custom entities using the same structure described above... so what functions can I use to have the same validation as above?... or even better if there is a way to use the filter function that when the medical rep tries to add a call record for a doctor, s/he should add this doctor from the filtered list using table or entity no 4 (MedRepDoctorList).

thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-yamao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi mokhawaja,

Have you create a relationship between these custom entities?

Please check this documentation about “Build a relationship between entities” for a reference:
https://powerapps.microsoft.com/en-us/tutorials/data-platform-entity-lookup/

Best regards,
Mabel Mao

Was this reply helpful? Yes No
Verified answer

mokhawaja 239 on at

Like (2)

Report
Copy link

Link copied!
Hi @v-yamao-msft,
i actually spent all night trying with this (as i am still learning PowerApps) and eventually i figured it out...
yes the relationships are all setup in all these related entities, then what i did is the following:
1. i created a button that navigates to the gallery screen and at the same time makes a collection that filters only the doctors who are related to the current user (medrep)... also extracted the medrep id using the lookup function and stored in a textbox
ClearCollect(MyDocList, AddColumns(Filter('MedRep Doctor Lists', MedReps_FK.PrimaryId = Label7.Text), "DocID", Doctors_.PrimaryId))
2. also used the addcolumns function to extract out the primary id of the doctors lookup field.

3. then created a dropdown control inside the doctors datacard in the editform and set its items property to:

Filter(Doctors, PrimaryId in MyDocList.DocID)
and then set the update property to: dropdown.selected

so now when i click on the dropdown it shows only those doctors that the medrep is assigned to.

i hope this would help others build their own solutions... if you have any comments or suggestions to improve these formulas, it would be great!

regards,
Mohammad

Was this reply helpful? Yes No
mokhawaja 239 on at

Like (0)

Report
Copy link

Link copied!
UPDATE...
Hi @v-yamao-msft
I noticed that the filter function:
Filter(Doctors, PrimaryId in MyDocList.DocID)
return only 63 records! while the collection contains much more than that, i am not sure what it wrong with it and note that there is a blue line under the "in" operator

please advise

Regards,
Mohammad

Was this reply helpful? Yes No
mokhawaja 239 on at

Like (0)

Report
Copy link

Link copied!

Bad News... @v-yamao-msft

unfortunately... the collect function is not delegabe (which means it only deals with the first 500 records)... and even using the simple filter function it also becomes non delegable when filtering using the complex type lookup field...

in the above screenshot... the entity contains more than 2000 records, and to test the filter function i tested it against data that falls after the first 500 records and put only 5 records that meet the criteria in the first 500 records... and the result was only the first 5 records that are in the first 500!!!

i checked the delegation article in the documentation and could not find any information about this limitation in the filter function (which could also be present in other functions like lookup and sort!!)

so please guys... help us on this issue and recheck the documentation

regards,
Mohammad

Was this reply helpful? Yes No