Filter dropdown menus for Lookup Column in a form

[schwibach]

I'm really struggling with lookup columns.

I want teachers to be able to create no records of students.

But I want them only to be able to select the school they teach in and within that school only classes that they teach.

I have a Sharepoint list with lookup a column each for the schools and the classes and a person column for the teachers.

The current user is the what I want to use to filter for the school and the teacher and the school to filter the classes for the viable options.

I have also not managed to make the form submit a predefined value in a lookup column. So, I have a variable in the app, the text of which corresponds with an option in the form. I have tried 10 different things to make the form submit the value of that variable, but to no avail.

Please help! 🙂

[v-qiaqi-msft]

Hi @schwibach,

Could you please tell me that:

1. if you have an existing SP list where teachers, schools, classes, all correspond to each other?
2. if the LookUp columns of school and class allow multi select?

Do you want to filter school dropdown list and classes dropdown list based on the current user?

I assume that you have a current user, and then the LookUp dropdown list should be filtered so that the school and class should be populated in the Combo Box, right?

Please give me more details about your data source.

[schwibach]

Hello Qi,

Thank you!

1. Yes. I have one list "Lehrer in Klassen with three columns:

a: Lehrer (text for the teachers) 

b: Schule (Lookup with no multiselect from the list Corona_Test_Schulen)

c: Klasse (Lookup with no multiselect from the list Corona_Test_Klassen)

---

@v-qiaqi-msft wrote:

Do you want to filter school dropdown list and classes dropdown list based on the current user?

Yes, I want to filter the school dropdown for the current user and then the classes dropdown for the school and the current user.

I'm only using dropdowns, no comboboxes because they give me trouble on iphones.

[v-qiaqi-msft]

Hi @schwibach,

Are the Corona_Test_Schulen and Corona_Test_Klassen two different SP lists?

I am confused that your description does not tell me that you have detailed relationship among teachers, schools and classes.

---

@schwibach wrote:

1. Yes. I have one list "Lehrer in Klassen with three columns:

a: Lehrer (text for the teachers) 

b: Schule (Lookup with no multiselect from the list Corona_Test_Schulen)

c: Klasse (Lookup with no multiselect from the list Corona_Test_Klassen)

---

---

You could find there is no any relationships among them, all these three columns are in 3 different lists, how could I filter the school for the current teacher?

If there is no relationships, you could only select from multi options in the dropdown list, there would be no pre-fill value within the Dropdown.

[schwibach]

Sorry for the confusion:

There are 3 distinct SP-lists.

1. Corona_Test_Schulen (for the schools and their details).

The schools can enter the information for the school (e.g. address printed on Covid test results, email for contact in case of positive covid tests in a class of that school)

2. Corona_Test_Klassen for all the classes in all the schools.

The schools can select their school in an app and enter new classes that they want to use,

This has a Lookup column to the the first SP-list.

I created a calulated field for the class that's identical with the Klasse field because a dropdown field would not let me reference it otherwise (very strange, but it works now).

3. Lehrer_in_Klassen

Here, the schools can select a class that exists in their school and enter the teachers (people registered in the tenant) who work in a class. They will then be able to enter and review results of their students (and only theirs)

I think I'm close to a solution, or at least, I can formulate the issue better now:

I have created a collection colMeineSchulen.

As you can see "Schule" is nested inside that collection.

Inside that collection there is a record for the school with ID and Value

Now, I would like to create a table with all the distinct nested values for the field Schulen.

But I don't know if I can do that with distinct, forAll, FirstN(FirstN....

I'd appreciate any help you could give.

[schwibach]

Success!!!!

I managed to do it with this

OnVisible of the screen I create this collection:

ClearCollect(colMeineSchulen;Distinct(ForAll(Filter(Corona_Test_Lehrer_in_Klassen; Lehrer.Email=varUserEmail);Schule.Value);Value).Result)

And in the dropdown I just reference the colMeineSchulen.Result

Yeayyyyy!!!!! 

[schwibach]

**bleep** it!

Now the form dropdowns let me select the schools and the classes correctly, but they don't submit the form into the SP list correctly.

It seems that the Form wants this schema

But in my dropdown, i only produce the values.

So, I guess, I'll have to manage to produce a table with the two columns filtered for the person in the column Lehrer.E-Mail (It's a person field). 

If you have any solution, I'd be most appreciative.

[schwibach]

I may be getting closer.

With this code I get a table that has an ID and but a record for each "Value".

RenameColumns(
    ShowColumns(
        Filter(
            Corona_Test_Lehrer_in_Klassen;
            Lehrer.Email = varUserEmail
        );
        "ID"; "Schule"
    );
    "ID"; "Id";
    "Schule"; "Value"
)

[schwibach]

With this

ClearCollect(colMeineSchulen;Distinct(ForAll(Filter(Corona_Test_Lehrer_in_Klassen; Lehrer.Email=varUserEmail);Schule.Value);Value).Result)

I get a table of the schools, but I get a record with the two fields in one column instead of a table with two columns.