cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhawkins10701
Helper I
Helper I

Using Excel data to populate web text field

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.

 

rhawkins10701_0-1635916528688.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

 

You can even create a List variable.

 

VJR_0-1635922108659.png

 

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)

VJR_1-1635922243808.png

 

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

 

View solution in original post

7 REPLIES 7
VJR
Super User
Super User

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 CodeAccount Number
ABCD1234
PQRS5678
JRPB1111
JRPB2222

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 CodeAccount Number
AVRA464377
AVRA466451
AVRA465833
AVRA466430
BHSL324600
BHSL673365
JRPB303435
JRPB303444
JRPB323286
JRPB303476
JRPB322699

- 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.

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.  

 

You can even create a List variable.

 

VJR_0-1635922108659.png

 

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)

VJR_1-1635922243808.png

 

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

 

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

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%

rhawkins10701_6-1636037784481.png

 

- 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!) 

rhawkins10701_0-1636035428136.png

 

See example of data below.

 

Client CodeAccount NumberFile Name
JRPB1234ABCD
JRPB5678EFGH
JRPB1111IJKL
JRPB2222MNOP

 

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.

 

rhawkins10701_7-1636061237590.png

 

 

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. 

 

 

 

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Top Solution Authors
Top Kudoed Authors
Users online (4,314)