SharePoint Power App form - multiple lists

[Kyzivat]

Hello,

I am making customized SPO Form, where I need to enter data into 2 lists at once, but one of the list has lookup to the other list.

The thing is that there may be occurrence, when I need to save data to 1 list before the other part of form tries to save the record as it has to lookup the 1st part. Also I am making it using "Form" element that has the fields of the particular list inside and so I need to have 2 of them, and they are both "scrollable" is there any nicer approach to avoid the "form"? 🙂

Thanks for any advice in advance.

[zmorek]

You can SubmitForm(Form) on whatever form you want first, then have its OnSuccess() property submit data to the secondary list, that's no problem. Sounds like you might be creating a race condition though, where sometimes you want X data to get somewhere faster than data elsewhere so you can use its result to produce the secondary list item, which makes me nervous.

You would need some way to indicate that, in this instance, you want this portion of the data to go first then the next data, then the next, etc. Power Apps sometimes doesn't always follow the exact order of the formula you create - it'll try to do some operations in parallel with others (even without using Concurrent()); this is because it's trying to be as quick and efficient as possible.

You can achieve this, but you need to be explicit - consider having the user check a checkbox which you can then use its value to determine how to submit the data. If that's true, then you can have your submit button do exactly what you want, when you want it.

Consider also why this race condition exists to begin with, is the structure of those datasources correct? Is there no better way to design them? Nobody likes a chance that data can fall through.

[Kyzivat]

Let me try to explain the problem.

Imagine you have a form (please ignore the absurdity), where you can request a car rental. On the form you specify the rental (date from - to) and the requestor. That is the 1st list. Now the 2nd list does store the info about the car (brand, model, registration number, etc.).

The form should allow user to add a new car if it does not exist yet or pick from a combobox with search an existing car. Now what I need to achieve is upload the car info first if it does not exist in form2 and then in form 1 use it's ID to populate lookup field that makes the record of rental connected to the just created car record.

As I have said please ignore the absurdity, it's just a principle of the issue, but the agenda is different.

[zmorek · Accepted solution]

Ah, I get it! That was a great illustration.

You could achieve this with the OnSuccess() Property of the form where the user can pick their dates, and select (or create) a new car (I like the idea of creating a car..). For exercise sake I'll call it the Registration form.

1. User submits the Registration form
2. The form submits back to its datasource, registering the user's dates for the car.
3. Form Registration's OnSuccess property can then submit the new car data to the other datasource (let's call it the Car datasource). This creates the new vehicle in the second table.
4. The next portion of the formula, still in the OnSuccess property of Form Registration, can patch its LastSubmit row with the new row in the Car datasources' ID. 

Without knowing more about each list, your OnSucess property of Form Registration can look something like this (forgive the hand-typed formatting and syntax):

With(
	{
	wPatchInfo: //temporarily save the returned table from the below patch to pass its ID to the registration table
		If(
			IsBlank(
					LookUp( //determine if the car exists in the second list
					CarDataSource, FormRegistration.LastSubmit.CarField = ColumnInCarDataSourceKeyColumn
						)
				),
					Patch( // patch new car data to car list from form information is no car exists
						CarDataSource, Defaults,
							{
							CarInformationColumn: FormRegistration.LastSubmit.CarInformationField, 
							CarInformationColumn2: FormRegistration.LastSubmit.CarInformationField2
							}
					),
					LookUp( //if car exists, keep original ID
					CarDataSource, FormRegistration.LastSubmit.CarField = ColumnInCarDataSourceKeyColumn
						)
	)},
	
Patch(//patch row in registration list with CarID
	RegistrationList, Form1.LastSubmit, CarIDColumn: wPatchInfo.ID)
	)

This can absolutely be cleaned up with another With() statement to avoid the double LookUps, however I'm at work right now and can't get it exactly correct for you. I wanted to just illustrate the ideas of:

1. Submit your form
2. Patch values to second list
3. Call data from the form's LastSubmit property to pass data from step 2 back to the original list

[Kyzivat]

Thanks a lot for your advise - I will give it a shot and I will let you know how I ended up 🙂