Update certain fields in Table1 with additional linked data from Table2 when submitting Forms?

[galos]

Hello,

Update: Please see my latest post as I have altered my question a bit

If you can consider the following two tables as an example

Table1 (This master table contains all relevant information)

Person                Street                      City                Zip          Make          Model         Year          Emergency Contact

Joe Murray         10 Alpine Way         Manhattan   10011       Honda        Civic            2001        Isabel Roonie

Mary Ruffalo      72 Ridge Rd            San Jose        94088       Ford           F150            1990        Jennifer Lari

Table2 (This table only contains people's names and their associated addresses)

Person                    Emergency Contact     Street                      City                Zip

Paul Timberline       Mary Struthers           51 Green Dr            Houston       77001

Tim Ridges              John Smith               18 Laurie Ln            Peacock        33402

Joann Leeds            Tony Plora                 32 Moose Pl           Juno              44504

(NOTE: the Person fields in both tables are referencing a "Person" type field in Microsoft Lists, so these are referencing actual people in AD, making them unique (based on email). As I begin to type their names, autocomplete shows me matching results in AD to select from)

I have a New form (form1) in Power Apps that is referencing data from Table1. So all Edit fields are from Table1. So when someone "rents a vehicle", all we would like to do is enter the Person's name in the Person field in form1 along with the additional vehicle info and click on submit.

What we'd like to see happen is the form would also pull the matching person's City, Street, and Zip info from Table2 (based on the matching person's names) and add that additional info into Table1's City, Street and Zip fields, along with all the other corresponding data. I would prefer not to do this with external mechanisms like flow because names and mappings can change over time, so it's important to capture this mapping at the time of entering data.

Is this possible? If so, can it be delegatable, as the Table1 List is rather large.

Thank you in advance!

[victorcp]

Hi,

It is possible, you can use a LookUp column to refer the other list (recommended) or you can use the function LookUp inside the app to get all the infomartion like this:

LookUp(Table2, PersonField.Email = ComboBox.Selected.PersonField.Email)

It will return all the columns from Table2

I hope it helps 🙂

Any doubts let me know

[galos]

(see other comment)

[galos]

OK, so I added two more datacards to the form and made them not visible. I put the following info into the datacard's "Default Selected Items" tied to the street:

If(FormRentalEdit.Mode=FormMode.New,{Value:LookUp(PersonToAddressMappingData,Person.DisplayName = DataCardValue3.Selected.DisplayName,ThisItem.'Street')}, Parent.Default)

but it doesn't seem do anything. Although the formula takes, the field doesn't add the street to the datacard value.

Should I be doing this a different way that would instead run a set of commands when I click on the "Submit" button to update this content?

[galos]

UPDATE

So I was able to figure out how to grab street and city and zip using lookups, by setting the default value of the pulldowns to:

{value: lookup(SPList, 'Person'.Email=datacard5.selected.email).Street}

however, this format doesn't work for setting the default value for a person. When I use the same method of looking up a person in a list and then populating the complex pull down of another Person field with either the person's name or email address, it does not update SPList data because it needs more info. I found out that if you use Patch, that you have to send all this info to the SPList when you want to submit a person:

{
  Claims: Concatenate(
          "i:0#.f|membership|",
          User().Email // Person email
          ),
          Department: "",
          DisplayName: User().FullName,
          Email: User().Email, // Person email
          JobTitle: "",
          Picture: ""
}

However, I'm not using patch, but rather a form and I do not know how to get the form to lookup and select the person's name automatically even if I supply it the right name. I've tried using both Display Name and Email but that didn't work and when I tried sending the object instead, the pull-down menu just said "[object object]" but nothing was sent to the SPList when I submitted the form.

Hoping someone else was able to figure this out or understands what I'm asking. I can't change my form to use patch as there are so many other conditions set to the existing form that I would almost have to start from scratch again.

Thanks again

[victorcp]

Ok, is it a Person field or a multiple Person field? because it is correct if you are setting it in the DefaultSelectedItems of the combobox

[DvdH]

Hi, Do u want to make a 3rd table with the data combined based on name? Then u could make a form from that. Do i understand u correctly? U could use a patch function to combine the 2 tables. If this is the case i'd look at the link below which tries this. But i think creating a relation(parent/child) would work better here see:
Solved: Re: Joining Tables in Power Apps - Power Platform Community (microsoft.com)


If this helps make sure to kudo it. If this solves your issue u can accept this as a solution

[galos]

Hi @victorcp

In the SPList column, it's set to type "Person or Group", so when you start typing someone's name in, it begins an AD lookup. It is single value only, not multiple person selection for this field

When I use the format:

{value: lookup(SPList, 'Person'.Email=datacard5.selected.email).Person.DisplayName}

or

{value: lookup(SPList, 'Person'.Email=datacard5.selected.email).Person.Email}

It displays the actual person's name inside of the pulldown menu, but when I submit the form, nothing is added to the SPList for the person column. However, if I do this for a non-person column in SPList, like an Address, it works.

I even tried sending the whole object by not specifying .Person.Email but rather just .Person, but the pulldown menu only shows [Object object] and even if I submit that, nothing is entered in the SP List either.

[galos]

thanks for your reply @DvdH .  I don't know that adding tables is necessary and I'm not sure how I would change the entire form to accomplish this, as the form itself has a tremendous amount of settings I'd have to re-enable and adjust. Hoping this could just be a quick way to pull data from one table and populate it into the other table.

[galos · Accepted solution]

OK, I figured this out - for anyone else who comes across this, the way to do this in a form is to use a TABLE format using JSON.

You want to set the "DefaultSelectedItems" to use this format:

Table(
    {
      Claims:"i:0#.f|membership|" & User().Email,
      Department: “”,
      DisplayName: User().FullName,
      Email: User().Email,
      JobTitle: “”
     }
)

The above will populate that field with the user who is logged in ( User().email and User().FullName)

Note, that while Department and JobTitle are not required, the Claims Value is and *must* start with that string and end with the person's email.

Hope this helps someone else out there.  Thank you all who replied to this post!