Getting an array from excel table (with 2 filters) into a Json array (within an object)

(0) Share

Report

Posted on by schwibach

2 Moderator

Hello,

the flow is about tasks that need to be done when a new student signs up at a school.

I have two excel tables

- one with the responsibilities for each class (one row per class), columns for the different responsibilities (head teacher, librarian, o365-admin, etc.)

- one with the teachers who teach in each class (one column 'class', one column 'teacher')
there are 4-7 lines per class (one for each teacher)

I would like to create a Json object variable for the student (after the new new student's data is entered in a form) and tap into the two excel tables (so I can create tasks for the different people responsible for him).

What I'm struggling with:

I want to create an array with the e-mail addresses of all the teachers in his class (the second table) minus his head teacher (listed in the first table with the responsibilities because he will get a different e-mail)

My questions:

How do I get the data from the lines in excel into the array (with the two filters)?

Is it a good idea to create such a variable or should I create an excel file/worksheet/table for each student and tap into the data from there? (in which case: How do I get the multiple e-mails into the other excel table?)

Should I look into business process flows for a flow that handles multiple tasks and e-mails or is it good practice to handle that with one flow?

Thank you guys a lot in advance!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Sort by

schwibach 2 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I've tried the following.

This only gave me back the e-mail of the last teacher in the list, not all three.

Ultimately, I'd like to get back two because I want it to filter out one from the other table.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, I can see what happened.
It wrote over the array in every iteration.
So, I'll try 'append array' or something like this next.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, I'm 2 steps further.
I can now managed to get all the e-mail adresses into an array by doing the following:

and I managed to do one filter:

What I could do is:
Do a condition now that "if the teacher is head teacher, then give him task head teacher" and "else send information e-mail"

Somehow that does not seem like a clean solution.
I tried to do a second filter in ODATA connected with "and" and using the operator "ne", but that only gave me an error. I read that you can't use two filters in ODATA on one column in excel.

But generally, I do think I have a solution for the problem, just not a very elegant one.

I'm very curious what you guys think how I should build that sort of thing.

Was this reply helpful? Yes No
v-yiwenxie-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @schwibach

First, you can only use one filter in that action:

Go to the link to see limitations in excel online business connector: Excel Online (Business) - Connectors | Microsoft Docs

But, if you don’t think it troublesome, you can use the action ‘filter array’ to minus the head teacher from the variable ‘teachers’.

Secondly, I’ve created a flow that uses the first filter you want:

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Wow! Thank you so much! You're the best!

I'll try it this weekend when I get a little time to breathe.
May I ask what the last step is for? Declaring teachers as a null-variable?

Also: You created the array teachers as a separate array and then put it inside the array class-divided responsibilities and teachers. Is that of any value for the rest of the flow or is it meant to make everything 'clean'?

And one more thing: Maybe, I'm overlooking something, but I don't see the step where you filter out the head teacher from the list of teachers in the array teachers. It's not critical though because I might be able to do it (as long as the array is a separate variable. I don't know how to do it if it's an array wrapped up in another array.

Was this reply helpful? Yes No
Verified answer

v-yiwenxie-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @schwibach

To understand the last step:

The 'set variable' action will reset the variable, which erases all the content formerly exist in the variable.
The 'append to array variable' action will add new content to the variable and retain the content formerly exist.

The last step of setting variable to null is in the 'apply to each' loop.
If the last step doesn't exist, then the array 'teachers' shown in class2 will contain the teachers in class1, the array 'teachers' shown in class3 will contain the teachers both in class1 and class2, and so on.

So, to make sure the variable 'teacher' only contains teachers for specific class each time, the variable should be set as null in last step in the loop.

To filter out the head teacher form the array 'teachers':

You just need to add a condition as below, which update the 'Apply to each' action before:

Keep careful when you choose dynamic content in the picture below:

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No