Submit to Multiple Forms with Different Datasources

[PPanfili]

Hello everyone!! 

I have been creating an App, for a school I am working in; in order to submit Discipline reports for students.

If you look at the attached screen, you will see various fields, inputting various types of information, into different datasources.  In total there are about 5-6 different spreadsheets behind this app.  In many of the spreadsheets, information will be duplicated.  For instance, Last_Name & First_Name are in all of the datasources.  I know this maynot be ideal, however, I have to keep it like this.  There are unique fields, which would be Last_Name, First_Name, Date, Incident, Discipline_Given.

My question is this.  Lets say someone inputs all the data on the attached screen; and then sends the data to the various datasources.  Then they realize that they made  mistake, perhaps, they put in the wrong grade or something.  

What would be the best method to Edit this information, in all of the Datasources.  Without have to do each one seperatly?  Basically, if I create an EditForm screen, would I be able to have the user search for this Record, and then edit the field, and then have it post to all the proper places?  I understand how to update only one of the datasources, but I want to be able to search, and update them all at once; to prevent redunancy, and to make it easier for the user.

Attached is a copy of the screenshot of the data that is entered it is tied to the following spreadsheets:  All, Log, Discipline, ISAP, Info...

I hope someon can shed some light on this

[LukeW-MSFT]

Hello!

Thanks for the thorough explanation of your situation! It sounds like the answer for you may lie in the Form.OnSuccess property. If a form submission succeeds, it sounds like you want to update the redundant data in other data sources (for example, last name is updated in DataSource A, so you want to update last name with the new value in DataSource B).

To test this out, I built two very similar DataSources ("lucaswh-Menu" and "lucaswh-Hamburgers") that have overlapping data. I have an edit form that points to the Hamburgers data source. When that is updated, I want to also update the corresponding record in the Menu data source. I was able to do this successfully by using the Patch formula:

Form1.OnSuccess = Patch('lucaswh-Menu', LookUp('lucaswh-Menu', Title = "Patty"), {Amount: LookUp('lucaswh-Hamburgers', Title = "Patty", Amount)})

After Hamburgers is successfully updated, we patch the Menu with the corresponding value from the updated Hamburgers data source.

My example only works for one row (Patty) and one column (Amount), but you could extend this to update the whole record by changing the last argument to Patch to be something like {Val1: Lookup(DS, Title, Val1), Val2: Lookup(DS, Title, Val2), etc...}. 

Hope this helps!

[PPanfili]

Luke,

Thank you for this response, as this is a major drawback on what I am buildling.  I am having a bit of trouble understanding exactly how to write this out in my situation though.  I am a little confused with the part that says Title = "Patty" in the formula?  This has me hung up, because it is a title of a column? Or textbox?  

So here is a better representation of what I am working with.  To start I have three data sources: All, Discipline, ISAP.  Attached see a start of me creating a form to edit all three tables upon update.  How would my code work?  

Patch('All', Lookup('All', Title = "?????"), {Grade: Lookup('All', Title = "???????", Grade)})

I also, have never used the OnSucces function.  Does this mean that after I press the Button, it triggers?

To be clear, I need the user to be able to come to this screen; search for data that they already inputted on a previous screen, which I am accomplising by the Dropboxes in the headings.  Once they locate this data, based on last name and date.  I need them to be able to change any of the columns associated with this record.  And I need that change to be reflected across all the Datasources.  

This seems very complicated to me, and I hope it can be accomplished.

[LukeW-MSFT]

Hi again!

Apologies for the unclear example! First, take a look at the following documentation:

LookUp: https://docs.microsoft.com/en-us/powerapps/functions/function-filter-lookup

Patch: https://docs.microsoft.com/en-us/powerapps/functions/function-patch

In my example, "Title" is simply the name of the column, you might want to use "Last_Name" instead to update all records for a given Last Name. In your case, let's assume that the DataSource behind Form5 is "All". Then if you set OnSuccess to be:

Form5.OnSuccess = Patch('Discipline', LookUp('Discipline', Last_Name=DataCardX.Default), {Grade: LookUp('All', Last_Name=DataCardX.Default, Grade)}); Patch('ISAP', LookUp('ISAP', Last_Name=DataCardX.Default), {Grade: LookUp('All', Last_Name=DataCardX.Default, Grade)})

where DataCardX is the data card for the Last_Name field (you can click around the data cards on the left side to figure out which one this is - I can't tell from your photo).

Assuming the Button calls SubmitForm(Form5) and the SubmitForm call completes successfully, yes, this will trigger an update of the Grade field in the "Discipline" and "ISAP" data sources to match that of the "All" data source. I hope this helps!

[PPanfili]

Luke,

I greatly appreciate your assistance on this, as I am a novice, and this is way over my head.  We are getting closer, and I provided you with a bigger screenshot so you can see what I am working with.

I also added the second from on this screen shot, this is Form1, on the bottom right, which is tied to the ALL table.  As you can see in the picture, various columns overlap.  Last_Name, First_Name, Grade, Points, and a few others.

I added your formula to the onsuccess of Form5, which is tied ot the datasource Discipline.  Both of these forms are filtered by the dropdowns at the top.

When changing something in Form5, and hitting the button, the data is indeed updated in Discipline Datasource, but not in the ALL datasource, or any other datasource for that matter.  As you can see in the attached screenshot, I actually updated the grade in Form5, and the change is not reflecting in Form1, nor is it reflecting in the backend datasource.  

My question is how does it know, which record is the appropriate one to update in the other form1?  Also, what if another field is updated, besides grade?  How will it know to update everywhere else?

Again, I feel like this is an extremely complex situation, basically because the way the data is set up, but unfortunatly I cannot change that.

I hope we can arrive at an conclusion on this, as it is way above my paygrade at this point.

[LukeW-MSFT]

Glad to hear we are getting closer. 

Since Form5 is tied to the "Discipline" datasource (I thought it was tied to "All"), the formula is not correct. The correct formula should be:

Form5.OnSuccess = Patch('All', LookUp('All', Last_Name=DataCard18.Default), {Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade)}); Patch('ISAP', LookUp('ISAP', Last_Name=DataCard18.Default), {Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade)})

I would expect this formula to result in Form1 updating as well. Let me know if it still does not work as expected.

---

My question is how does it know, which record is the appropriate one to update in the other form1? 

I need to know what the datasource backing Form1 is to answer this. You already told me Form5 is backed by "Discipline". And you want Form1 to update all the data sources in a similar manner to Form5 right? Let me know.

---

---

Also, what if another field is updated, besides grade?  How will it know to update everywhere else?

You will need to add more parameters (separated by commas) inside the "{ ... }" in the Patch function. For example, in Form5.OnSuccess it will look like: Patch('All', LookUp('All', Last_Name=DataCard18.Default), {Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade), Col2: LookUp('Discipline', Last_Name=DataCard18.Default, Col2), Col3: LookUp('Discipline', Last_Name=DataCard18.Default, Col3)}); Patch(...

If you are still unable to get it working (reasonable, as the formulas are getting pretty long at this point), please reply with the following and I can guide you towards your exact solution (and tell you why it works, of course):

1. A list of all Forms in your PowerApp that you want to use for this multi-datasource updating process and the datasources behind those forms

2. A list of columns for the 3 DataSources (e.g. Discipline: Last_Name, First_Name, Grade, etc...)

[PPanfili]

This formula place on OnSuccess of the Form5 is still not resulting in Form 1 updating.

Form5.OnSuccess = Patch('All', LookUp('All', Last_Name=DataCard18.Default), {Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade)}); Patch('ISAP', LookUp('ISAP', Last_Name=DataCard18.Default), {Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade)})

Form 5 is tied to Discipline.  Form 1 is tied to DataSource All.

Attached is a list of all my datasources. 

As of now there are only 2 forms; because I was just waiting to get them 2 working, then was just going to add to code.  I will make up fake forms now just to see if you can get code working.   

Form5 = Discipline

Form1= All

Form2=ISAP

Form3=LOG

Here are the columns in the the Datasources...

Discipline----

Last_Name

First_Name

Grade

Period

Administrator

Date

Date_Of_Offense

Code

Points

Period_Given

Student_Offense

Discipline_Given

Date_Seen_Admin

__PowerAppsId__

All

Last_Name

First_Name

Grade

Gender

Ethnicity

Infraction

Incident_Date

Point_Assigned

Discipline_Assigned

Actual_Resolution_Date

Points_YTD

__PowerAppsId__

ISAP

Last_Name

First_Name

Grade

Start_Date

End_Date

Period

1

2

3

4

5

6

7

8

9

Administrator

NOTES

Todays_Date

__PowerAppsId__

LOG

Last_Name

First_Name

Power_School_Grade

Grade

Points

Disc_Assigned

Start_Date

End_Date

ISAP_Periods

Code

Administrator

Gender

Ethnicity

Date_Seen_Administrator

Today_Date

Student_Offense

Date_Of_Offense

Period_Of_Offense

Reporting_Teacher

Acutal_Resolution_Date

Prepared_By

__PowerAppsId__

I thank you again for this help, as there is no way I would be able to work out this complex formula...I hope in the end it will work; but it just seems really complicated to me.

[LukeW-MSFT]

Let's try to get the two forms you have right now working first.

Form5.OnSuccess = 

Patch('All', LookUp('All', Last_Name=DataCard18.Default), {First_Name: LookUp('Discipline', Last_Name=DataCard18.Default, First_Name), Last_Name: LookUp('Discipline', Last_Name=DataCard18.Default, Last_Name), Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade)}); Patch('ISAP', LookUp('ISAP', Last_Name=DataCard18.Default), {First_Name: LookUp('Discipline', Last_Name=DataCard18.Default, First_Name), Last_Name: LookUp('Discipline', Last_Name=DataCard18.Default, Last_Name), Grade: LookUp('Discipline', Last_Name=DataCard18.Default, Grade), Administrator: LookUp('Discipline', Last_Name=DataCard18.Default, Administrator)})

This should result in the "All" and 'ISAP" data sources being updated to reflect the changes in the "Discipline" data source. This should update the columns First_Name, Last_Name, Grade, and Administrator (Administrator for "ISAP" only, since it doesn't exist in "All")

Form1.OnSuccess =

Patch('Discipline', LookUp('Discipline', Last_Name=DataCard18.Default), {First_Name: LookUp('All', Last_Name=DataCard18.Default, First_Name), Last_Name: LookUp('All', Last_Name=DataCard18.Default, Last_Name), Grade: LookUp('All', Last_Name=DataCard18.Default, Grade)}); Patch('ISAP', LookUp('ISAP', Last_Name=DataCard18.Default), {First_Name: LookUp('All', Last_Name=DataCard18.Default, First_Name), Last_Name: LookUp('All', Last_Name=DataCard18.Default, Last_Name), Grade: LookUp('All', Last_Name=DataCard18.Default, Grade))

This should result in the "Discipline" and 'ISAP" data sources being updated to reflect the changes in the "All" data source. This should update the columns First_Name, Last_Name, Grade.

Please try these and let me know the results!

[PPanfili]

To start, I greatly appreciate this.  It is almost working as I need it too.  

On Form_5, if I change the Last_Name, First_Name, Grade, and Admin, they are in turn changing on the ISAP Datasource, but not the ALL Datesource.  

If I change the Last_Name of Form5, it results, in a Hiccup of sorts, which I believe is because Form1 is tied to ALL, and those fields are not updating, so Form 1 results, just disappears now, because there is no Last_Name Match, because it changed only in the ISAP and Discipline Datasources, but not all.

Once that hiccup is worked out, my next task would be to just be able to understand the formula, so that I can add fields to be updated through all the datasources.  

My thought and hope is the, that the Screen will work backwards via the Forms....So I will have all the Fields in Form5 Datasource showing.  So if someone were to update Last_Name, it would filter down to the Datasources on Form4, Form3, Form2, Form1.  So On the screen there will only be one instance of the Last_Name Column.  

Then, from there, whatever is in the other forms will show only once, and update the rest of the forms... 

Basically, I dont want duplicate fields showing on the screen, just 1 field per item that is necessary.

[LukeW-MSFT]

Your explanation of the "hiccup" sound reasonable to me. Really, the Form5 update should update the "All" as well as the "ISAP" Datasource so that shouldn't be happening in the first place.

Form5 issues:

I double checked the Form5.OnSuccess formula and it looks correct to me. Still not sure why it is only updating "ISAP". There may be a data issue in your databases which I am unable to diagnose from here.

Also, keep in mind that my formulas assume that last names in your data bases are unique for each student. If they are not, perhaps you may want to change all the code that says "Last_Name=DataCard18.Default" to "First_Name=DataCardX.Default && Last_Name=DataCard18.Default" where X is the number of the data card that contains the last name.

Form1 issues:

I realized that the formula I gave for Form1.OnSuccess may not be correct since it references DataCard18, which is in Form5, not Form1. Please subsitute DataCard18 for whichever Data Card in Form1 contains the Last_Name field info.

Hope this gets you closer.

-Luke