Related Table Lookups

[OddHelge]

Hello!

I've gone trough a large amounts of other posts and documentation, but still don't seem to find a solution.

Situation
I'm currently working on a custom Code Component that resembles a Gantt Calendar, that is going to be used in our PowerApps.

To further develop the handling of data that is going into the component, I am looking for a "query" to use as an input for the Code Component.
I'm using lists in our SharePoint set up as a related table, where I want to filter for specific data(assignments).

Problem
The problem I'm having is this query, and how to do this in PowerApps. Then I need to use what this outputs as demo data input for my current Code Component project, to work with how to handle the data.

The query I'm looking for can be related to a sql query:

SELECT
  *
FROM
  Assignments
  INNER JOIN Subjects ON Assignments.subject.id = Subjects.id
  INNER JOIN FieldPrograms ON Subjects.fieldProgram = FieldPrograms.id
  INNER JOIN Fields ON FieldPrograms.field.id = Fields.id
WHERE
  Fields.id = 1

Short

I need a little example on how to use all the "LookUp()", "Filter()" and "FirstN()" properties to achieve a type of sql(INNER JOIN) result.

Conclusion 
If there is anything I missed or you want more data or have any questions; please don't hesitate to ask!
Any help clarifying or pointing me in the right direction is greatly appreciated, and Thanks in advance!

Edit: For others looking for answers, the solution for my problem specifically is Message#5. But I accepted Message#2 as the solution, as it solved my problem by studying it!

[v-bofeng-msft · Accepted solution]

Hi @OddHelge ,

Please try:

Filter(
ForAll(
    Assignments,
    Patch(
        ThisRecord,
		LookUp(Subjects,id=Assignments[@Subject].id),
		LookUp(FieldPrograms,id=LookUp(Subjects,id=Assignments[@Subject].id).fieldProgram),
		LookUp(Fields,id=LookUp(FieldPrograms,id=LookUp(Subjects,id=Assignments[@Subject].id).fieldProgram).field.id)
	)
),field.id=1)

Best Regards,
Bof

[OddHelge]

Hello @v-bofeng-msft and thanks a lot for your reply!

This was extremely informative, I only needed to tweak a tiny bit on the formula you provided to remove the errors!
Not entirely sure if it works yet, but now I have a much better understanding on how to do the formulas. And by the looks of it I get a decent amount of records.

No error formula:

Filter(
ForAll(
    Assignments,
    Patch(
        ThisRecord,
        LookUp(Subjects, ID=Assignments[@subject].Id),
        LookUp(FieldPrograms, ID=LookUp(Subjects, ID=Assignments[@subject].Id).fieldProgram.Id),
        LookUp(Fields,ID=LookUp(FieldPrograms,ID=LookUp(Subjects,ID=Assignments[@subject].Id).fieldProgram.Id).field.Id)
	)
),field.Id=1)

(Not sure if this is correct, but this removed all the errors I got).

Thanks again for responding, this helped out a lot! And I think I got a better understanding of how to query trough the lists.

Any thing I can do here to help you out, please let me know.

[OddHelge]

Hi again!

After testing with the formula, it seems to work to an extent.

It goes trough the assignments, and filters out the ones that doesn't end up with a relation with the fields ID. But the problem is that its not putting the corresponding assignment record into the records, it puts a combination on all the other lookups instead.

I've been playing around with it but it doesn't seem to work. The LookUp I came up with is the following:

LookUp(Assignments, subject.Id=LookUp(Subjects, fieldProgram.Id=LookUp(FieldPrograms, field.Id=LookUp(Fields, ID=1).ID).ID).ID)

But this only returns one of the same item 3 times, as its 3 records in Assignments that has a relation ending with the Fields ID of "1".
So If I set the text of say a ListBox to be the Title of each entry, I get the same title on all entries as their data is all the same.
I seem to get the correct description for each entry, but all the other data is false, such as the needed data provided in the attached image.

[OddHelge · Accepted solution]

Hello once again! 

Hopefully this is the last message I'll wright on this post, as I've figured it out!

Finally I get the records that I want, where the check through all the relations are correct.
Again I don't feel like this is a very optimal way of doing it, but this gets the job done for the time being.

The solution? formula:

Filter(
    Assignments,
    subject.Id = LookUp(
        Subjects,
        Assignments[@subject].Id = ID && fieldProgram.Id = LookUp(
            FieldPrograms,
            ID = Subjects[@fieldProgram].Id && field.Id = LookUp(
                Fields,
                ID = 1
            ).ID
        ).ID
    ).ID
)

(This goes trough all the records in assignments, and checks if the record's relation trough all the lists are valid, and if the relation isn't there the record being iterated trough simply does not display).

A huge thanks again to @v-bofeng-msft for giving me that very informative example that allowed me to figure this out.