Filter gallery issue

[rmzn]

Hi,

I have a app connected to D365 Fin&Ops. I am using two entities and these are related (CPMProjProcessAppStatus.RecId1 = ProjProcessAppStatusAssignedUsers.Reference) with each other. ProjProcessAppStatusAssignedUsers shows that which CPMProjProcessAppStatus record assigned to who.  One CPMProjProcessAppStatus record could be related more than one ProjProcessAppStatusAssignedUsers record. I have a gallery that uses CPMProjProcessAppStatus entity, and need to filter via ProjProcessAppStatusAssignedUsers  entity. I wrote a formula like below, but it didn't work. How formula should I write? Thank you for your comments.

Filter(CPMProjProcessAppStatus, RecId1 in Filter(ProjProcessAppStatusAssignedUsers, 'User ID'=Text(Office365Users.UserProfile(User().Email).MailNickname)).Reference)

[dpoggemann]

Hi @rmzn,

I took your example here and created a sample application with the following:

Parent table - would map to your CPMProjAppStatus table

Child table - would map to your ProjProcessAppStatusAssignedUsers table

I then created a gallery that would display the distinct Parent records that match the email of the signed in user on the child records. 

The following shows the Items code I used Distinct(Filter(Children,Email=User().Email),'Parent')

and then in the Gallery itself I referenced the Title and Subtitle from the Parent's record 

ThisItem.Result.Name 

ThisItem.Result.'Created On'

I tested with two of the same emails on a child record to verify it filtered to only one parent record and did not duplicate (this is why I needed the distinct).  Show records in child table in screen shot to show you what I tested.

The data environment was the Dataverse so hopefully this is ok...

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks much,

Drew

[rmzn · Accepted solution]

Hi @dpoggemann, 

Thank you for your reply. If I have one table I can filter as you did. For two table the formula doesn't give any error but doesn't take any record. I tried to make a collection like your table, this time details form didn't work. Then I found another solution; we made new entity with two tables and I could do everything 🙂

thanks,

[dpoggemann]

Hi @rmzn ,

If your issues is resolved you might want to mark resolved.  I am confused on how you solved, I had to do the above approach to only show the records on the parent table where the user was related to the child records so if there were 3 parent records and all the children records only related to two of the parents with the user's information on the child this is the only way I could show the 2 parent vs. all 3.  Please share your solution if you wouldn't mind.

Thanks,

Drew

[rmzn]

Hi @dpoggemann ,

As I said, we create a new entity. Because I couldn't run filter formula with two entity. The formula didn't give any error but didn't work. You have one table and don't have any error. Like you I create a new entity that unify the records from two tables. 

Thanks,