A PowerApp, PowerAutomate and Two SharePoint Lists Email Flow

[TechBrain64]

I'm really not sure if this is the right place to post; Power Automate or Power Apps. I guess if no one responds I'll move it to Power Apps. 

Here is what I'm looking to accomplish. I'm building a case management app where members of my team take ownership of a case and at various points of said case emails are sent to clients. Much of the underlying app is coming along fine, however, I'm needing assistance with what I call the E-mail Communications pop-up (see image). This pop-up is triggered when a case owner clicks a button on the case details screen.

SharePoint Datasource: I have two SP lists: Case Management (CM) and Email Comms (EC). EC has a RefID -number- lookup column back to CM's CaseNo -number- column. The EC list has a "EmailStatus" -choice- column with Initial, Schedule, Confirmation, Follow-Up, Reschedule and Completed.

Workflow:  When a case owner clicks a (purple) button I'd like a flow to fire off that particular email template with pertinent case information to the client and/or alternate contact, the option box for that email type is checked and button disabled, IF for some reason an email has to be resent, case owner can reset status of button. 

Currently, the above image is just buttons, labels and a gallery tied to EC list for the options box and reset icon. However, I'm considering switching to full gallery. But I'd welcome the expertise of community as to how I can achieve my vision.

[v-jefferni]

Hi @TechBrain64 ,

Would you like to create new records in EC list based on the clicking of buttons within Power Apps?

If so, you just need to create a new record and send emails, below flow steps will be enough:

Detail steps:

Expression for RefID is:

first(outputs('Get_items')?['body/value'])?['ID']

At last, you need to append the flow on all of the buttons OnSelect to set the two parameters to pass to flow.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[TechBrain64]

@v-jefferni ,

Thanks for sharing. In your example it looks like you are referencing two PA variables in the filters: GetItems & CreateItems, but I don't see them in flow. How did you derive the variables?

[TechBrain64]

@v-jefferni,

I've somewhat followed your lead and created the below flow so far:

 

Which does create new record in list2 (Comms Tracker, see image below), but it's not creating the RefID field which is a lookup value linking back to list1 (Case Management). In the step above I created custom value using the CaseNo variable thinking it would update RefID field; not what's happening. I guess I'll have to use an expression in the Email Status so it updates both fields? The Email Status column is a choice list with each status which I'd like to allow users to reset a status in case for some reason an email has to be resent. I manually created the first record with it's statuses.

By the way if you don't mine, what is the difference in use case between GetCaseNo_Value and vCaseNo? I understand the later is the variable, but when would one use the _Value choice in formulas?

[v-jefferni]

Hi @TechBrain64 ,

It requires a look up list ID column value in the LookUp column, you can add a step "Get items" with filter query to filter the list on the case number and get the corresponding ID value then use it in this field with the help of first() function in its expression.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[v-jefferni]

Hi @TechBrain64 ,

Have you solved this problem?

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[TechBrain64]

@v-jefferni,

Given the lack of details in your last response, no. I'm not ignorant to tech, but PA is not yet on my resume.

[v-jefferni]

Hi @TechBrain64 ,

I meant that you will need the ID column value of the record that this column is looking up for to populate in the RefID Id field:

When selecting "Custom value", you will be able to write expressions on the right:

you can select the dynamic content from in this panel, and the final expression would be like:

first(outputs('Get_item')?['body/YourLookUpColumn/Id']) //select outputs from dynamic contents

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[TechBrain64]

@v-jefferni 

I'm having a difficult time getting my head around your suggestions and moving forward with a viable flow. For this reason, I've decided to take a different approach. I created a new SP list "Communications" with the following fields:

RefID              =     Lookup to referenced "Case No"

Initial              =     Yes/No                  PowerApp button that triggers email template by Automate

Schedule        =     Yes/No                  PowerApp button that triggers email template by Automate

Confirmation =     Yes/No                  PowerApp button that triggers email template by Automate

Follow-Up      =     Yes/No                  PowerApp button that triggers email template by Automate

Reschedule    =     Yes/No                  PowerApp button that triggers email template by Automate

Complete      =      Yes/No                  PowerApp button that triggers email template by Automate

When either of the corresponding buttons are pressed (typically in order) the flow is triggered. For example, I want to send the "Schedule Email" to the contact of the case, I press the button triggering flow, PowerApps passes the current CaseNo and Button.Text value (which are the same as listed above), Automate gets the CaseNo record from SPList1, the email template is selected based on the Button.Text value, email dynamic data fields are derived from CaseNo record, email sent, SPList2 is updated where RefID equal CaseNo and Button.Text value set to Yes.

In the above image I am required to provide an ID number, however how am I to do that if there isn't already a record for the related case? What would be the expression to update the RefID and Button.Value.

[TechBrain64]

@RandyHayes, thanks for your help with my Update Gallery Record Field OnSelect Button. I was even able to include updating case status with the button click. Now I'm hoping to get your assistance with my Automate workflow.