Unanswered

"AND" function for selection from List rows Excel in a Approval flow

(0) Share

Report

Posted on by chosen67

Hi, I have built a flow for applying for exams. It works as follows:
- Student scans QR and fills out a Forms
- In the case of a 1st or 2nd attempt, the teacher approves the request
- In the event of a 3rd attempt, the application must also go through other actors (Examination Committee)
- The flow contains reminders and confirmation emails
- The application is written into an Excel file with which the exam agency schedules the exams.
During a test with students and teachers, it turned out that students could easily apply for an exam for the second time. The Forms is used for many exams and must therefore be completed more often.

I came up with the following solution:

I compare the data from the Forms with the data already saved in the Excel file. I want to check whether the application meets 3 conditions: Student number (already filtered), the attempt (1st, 2nd or 3rd attempt) and the name of the exam. If all conditions are true, the outcome must be False. If one of the results is False in the “For each” action, the request should be automatically rejected. An email will follow with an explanation about the rejection of the application.

- The excel is read with “List rows present in a table”. The filter is the student number. In this case there are 2 rows that are returned.

- “Initialize variable” creates an Array where these variables are collected from Excel

- In the “For each”, the Condition action checks each returned line from Excel to see whether both conditions are met.

“Outputs” is a Compose action to collect all exam names in one string (always only one exam name is returned)

- Each outcome is added to the “VarOutputCheckExcel” with “Append to array”

- With the action “Check for duplicate requests” there must be no outcome of “For each” False to continue the flow.

What's going wrong?
It seems that only the first variable (Attempt) is considered. If it is different from what is already in Excel, the flow continues. If the “attempt” is the same, but a different exam, then False is also returned.
Now I have tried to check the checkboxes in Condition 3, but they remain unchecked when I save the flow. Copilot indicates that in that case there is an error in the flow. The question now is: where is the mistake?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

SunnyRKFOODLAND 101 on at

Like (0)

Report

Why not use the Conc function while adding forms data into Excel to check attempts by counting rows of that conc value in Excel.

Was this reply helpful? Yes No
chosen67 90 on at

Like (0)

Report

Best @SunnyRKFOODLAND,
Thank you for your comment. It's my first flow, so I'm a rookie 😉
Possibly that's why I don't understand your solution.
Because not only must the deletion of the 'wrong' data (a double registration) in Excel be prevented, the student must also receive a response (email) so that he can submit the correct request.
If your solution can do this, what would your solution look like? What should I remove and what actions should I add instead?

Was this reply helpful? Yes No
SunnyRKFOODLAND 101 on at

Like (0)

Report

Let me understand your requirement first
Student fills out the form
first name
middle name
last name
Dateofbirth
examname

if student fills out the data like below 3 different times
first name -A
middle name -B
last name -C
Dateofbirth -1/1/1999
examname -A
then there is 3 rd attempt of his trying for same exam so you need to send an email to him regarding this 3rd attempt
Am I correct?

Was this reply helpful? Yes No
chosen67 90 on at

Like (0)

Report

Thnx @SunnyRKFOODLAND for your help!
The following data is collected in Excel (with or without a concat function):
Columns:
C: how many attempts (from the Forms)
M: studentnumber (from MS-account; in this case my Nickname from the MS-Account)
Q: Name of exam
Only if these 3 variables all match the output from the Forms form, then the student has already had or requested this attempt for this exam.
Example: the student requests a 2nd attempt for the "IE Nederlands 2F Spreken" exam, which should be allowed according to Excel. Because the student number is the same and also the name of the exam, but the attempt is different (2nd attempt instead of 1st attempt)
But that does not happen because there is already a "2nd attempt" for another exam.
Explanation:
The flow turns Condition 3 into an OR function because the checkboxes remain unchecked.
And as in my first post: Copilot indicates that there is an error somewhere. And I can't find it 😞

Was this reply helpful? Yes No
chosen67 90 on at

Like (0)

Report

Here's an update on my search. The Initialize a variable geeft een error. I don't know what this error means and what consequences this error has. Perhaps this is the source of the errors in the flow?

Was this reply helpful? Yes No
SunnyRKFOODLAND 101 on at

Like (0)

Report

Do students need to select that if it is 1st attempt or 2nd attempt or 3rd?
Does every student have a One drive MS Account?

Just try my solution if it helps
considering below are the points you are looking for

1. Students can apply for any exam till 2nd attempt
2. If a student tries to attempt 3rd time for the same exam mail will trigger.
now when students update the data we need to bring that data to Excel like below
here, the first 5 columns are from MS Form and the last is calculated in power to automate as concatenate to get the unique value.

below is FLOW

now you can use the compose action value in condition to check where attempts are how many times and then you can proceed further if this is student's 3rd attempt then or else

Was this reply helpful? Yes No
chosen67 90 on at

Like (0)

Report

First of all: thank you @SunnyRKFOODLAND very much for your answer. First a quick response to your first 2 questions:
Students must indeed indicate whether it is their first or second time, which determines which version of an exam they will receive.
And yes, all students have an MS Account (school domain)
Now I'm going to see if your solution works 🙂

Was this reply helpful? Yes No
Verified answer

chosen67 90 on at

Like (0)

Report

It took some puzzling, but I'm done!
Your help was the push in the right direction 🙂
What I have done now:
With Compose action I have indeed created a unique value that is written to Excel. In case of a duplicate request, this unique value must be blocked.

In List rows I first filter on the student number so that not all rows (there may be more than 10,000) are retrieved.

Then filtered the output of the Compose action with the Filter array action.

Because it was not possible to operate the Control function with this, I added a Compose action that returns the number of rows from the Filter array.

Then I can set the Control action to: output eq 0, and no double input is possible.
So, my problem is solved!
Thanks a lot for the help @SunnyRKFOODLAND !

Was this reply helpful? Yes No