Help Designing Registration Workflow with Logic

[Anonymous]

Hello

I am new to powerautomate and am looking for help building a workflow for registering students for a course that has different price points. The price point is based on the affiliation of the participant, as well as the course section they select.  In total, there are 22 combinations of price point and course section; and there are 6 possible prices (750, 500, 375, 250, 125, 0). 

The registration steps are:

1. Participant submits a Form with a course section and sector affiliation.

2. Staff review Form to make sure participant has identified their sector affiliation correctly since this affects the price point (by comparing email address and sector selected from dropdown question)

3. Staff approve registration. 

4. PDF file is sent to office via email to request participant receive student ID.

5. PDF files is sent to participant via explaining how to pay for course with the price information.

I have the basic flow implemented as follows, but I cannot figure out how to add logic related to setting price point based on affiliation and section. 

1. Participant submits Form

2. Staff receives notification Form submitted.

3. Form fields populate a SharePoint List

4. Staff review item in SharePoint List 

5. Staff approves item in SharePoint List (Note: my institution hasn't implemented approvals properly, so I added a column to the SharePoint List called "Approved" which is a choice field that can be changed to Yes when approved)

6. Once approved, PDF is generated from Word Template and sent to participant with information

7. Once approved, PDF is generated from Word Template and sent to office with information

Ideally, the course rate would automatically be added to Item the SharePoint when the Participant submits the form based on the sector affiliation they select and the course section they select.  Is there a way to do this? 

Any help is appreciated. 

Thank you

Sandra 

[ScottShearer]

@Anonymous 

For the price point, I suggest listing all the price points in an Excel table.  Then you can simply lookup the price point based on your data.

Here is an example of one way to do this

Here is a blog post that discusses creating the PDF. 

That should be enough to get you started.  Let me know if you have additional questions.

[Anonymous]

Hi Scott,

Thanks for providing this information.  I was able to set up a flow to populate an Excel file but I still don't see how to add specific data based on selections in the form.  I want to automate this as much as possible, so I don't want to have to go into the Excel file and add the fee. The only thing I want to do manually is approve the registration, and I believe I have to do this since my institution doesn't support approval workflows in Office 365.

Also, regarding the PDF, I was able to do this already from my flows that used a SharePoint List.  I can only use built-in connectors and cannot use any premium connectors or enterprise connectors .  Our IT department doesn't support additional connectors. 

So I am looking for logic to add the fees to either a SharePoint List OR the Excel Table once a form is submitted to automatically add the appropriate fee to the row that has the form entry.  

For example:

Participant selects sector: Consulting

Participant selects course section: Program Manager

Fee = $750.00

Thanks for any additional help. 

Sandra 

[Anonymous]

update - I think what I need to do is add an office script to the Excel workbook that can add the course fee based on the columns: Sector and CourseSelection.  Then I can add the action to run that script after the step in my flow that adds the row to the Excel table.

Unfortunately, I do not know TypeScript!