Answered

Using Excel data to populate web text field

(0) Share

Report

Posted on by rhawkins10701

I have 2 columns of data that will have 2 variables (Client Code and Account Number). I need flow to run IF client code = variable then populate text field with this Account Number. I would need this to loop as there are multiple account numbers for a specific client. I understand I would use IF conditional action and a For each action but not sure how to write syntax.

First, I read from excel worksheet that produce variable %Datatable%.

Read online, if you want to access a specific column in a datable that contains column headers, use the %Variable[rowNumber]['ColumnName'] notation. So I used the below syntax in my flow.

Would I nest a for each action within an IF conditional action ?

In my case, if Client code column = JRPB, populate the account number in the text field.

Categories:

Power Automate Desktop

All responses (7)

Answers (1)

Sort by

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @rhawkins10701

Are you saying your excel sheet looks like the below, because you mentioned that there can be multiple account numbers for a specific client.?
If so which account number should be picked up when the client code is JRPB?
If not can you share the format of your Excel table and what needs to be picked up.

Client Code Account Number
ABCD 1234
PQRS 5678
JRPB 1111
JRPB 2222
rhawkins10701 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @VJR !

So in your case, account number 1111 and 2222 will need to be picked up.

See format of excel table below. So IF client code = JRPB then I need 303435, 303444, 323286, 303476, and 322699 account number to populate the text field on web page.

Although between each accounts there will be other actions. So once an account is selected some actions follow to download file. Once download complete, then cycle through same actions again with the next account number. So I am thinking I need a loop action for the account numbers.
Client Code Account Number
AVRA 464377
AVRA 466451
AVRA 465833
AVRA 466430
BHSL 324600
BHSL 673365
JRPB 303435
JRPB 303444
JRPB 323286
JRPB 303476
JRPB 322699
VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

- Have a new string variable. Lets call is AccountNumbersList
- Start a loop on the datatable
- Check if client code is JRPB
if yes then AccountNumbersList = AccountNumbersList & AccountNumber from the Datatable column & ","
(This is just a Pseudo code. Your syntax will be a bit different)
- when the loop finishes the AccountNumbersList string will have the list of all the required account numbers separated by a comma (notice how there is a comma in the end of the above code.

Whenever you need individual Account numbers from this string you can always use a split function based on the comma delimiter.

Post back what you come up with.
rhawkins10701 30 on at

Like
a
(0)

Report
Copy link

Link copied!

In this case I will only need 1 account number at a time.

Example: IF JRPB is found on list I need account number 1234 to populate in text field on web page. Once account number is populated have more automated steps to download a file. Once done with that account I will need flow to loop back and look for JRPB again on list for next account number. If found, populate again in text field and do same steps over again until no more JRPB accounts are found on list.
Verified answer

VJR 7,635 on at

Like
a
(2)

Report
Copy link

Link copied!
You can even create a List variable.

Yes a loop is used twice but it will make it easier for you to get one Account Number at a time while reading this list and perform your other webpage operations.

After running the first "For each" the list looks as below (only JRPB ones)

Now using the second loop in line number 10 you can read each Account numbers, perform your other actions to put it into another webpage one by one.

If you need the above code it is as below.
Make sure to change the Excel path and also I have removed space from column names like ClientCode instead of Client Code.

Excel.LaunchAndOpen Path: $'''C:\\Test\\Excel2Web.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance Excel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Variables.CreateNewList List=> ListVariable LOOP FOREACH CurrentItem IN ExcelData IF CurrentItem['ClientCode'] = $'''JRPB''' THEN Variables.AddItemToList Item: CurrentItem['AccountNumber'] List: ListVariable NewList=> ListVariable END END LOOP FOREACH IndividualAccountNumbers IN ListVariable Display.ShowMessage Message: IndividualAccountNumbers Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed END
Benny_1857 124 on at

Like
a
(0)

Report
Copy link

Link copied!

For my understanding on this case, why not use power query clear and reset the data format that you want to input into web, then use power automate desktop operate the input work for each client code once with multiple account number.

1. code a PQ for this file
2. use PA for desktop set up UI for excel operation under PQ enviroment
3. use loop read cell from excel and write into web
rhawkins10701 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @VJR !

I think I understand the variables a lot better now and how to use it. I really appreciate your help with this! I have another variable that I am going to add which is %FileName%. Once, all the actions are completed and file is downloaded. File will be in download folder and file needs to be renamed and moved to particular destination. Then loop back through the same actions for all other JRPB account numbers to download, rename file, and move file. Until there is no more JRPB accounts to download.

The actions I took:

- Created new list %FileName%

- Then under the 1st nested action I use Add item to list action CurrentItem ['File Name'] to list %FileName%
(I had to keep space in order for PAD to read column name and it worked!)

See example of data below.

Client Code Account Number File Name
JRPB 1234 ABCD
JRPB 5678 EFGH
JRPB 1111 IJKL
JRPB 2222 MNOP

Currently the first account number and file is renamed and moved to folder. Which is perfect! When actions loop back around for the second account number (5678) file is named ABCD again and doesn't move to designated folder since file name is already there. See actions below.

- I created another for each action for each file name. But currently when it runs, it doesn't go to the next file name on list.

I need account number 5678 when selected to name file EFGH. Need each file to be renamed from the corresponding account number that was downloaded. I did an exit loop because I didn't want it to loop until the first actions to download file is done.