Linking 2 columns

[RvdHeijden]

I was wondering if it's possible to 

I've got an app where you can add items to a sharepoint list, nothing spectacular.

When adding a new item to the list there are a few fields you have to fill in, 2 of those items are choice columns

The easiest way to describe it that it's a Category column and a Subcategory column. When you choose a Category the choices in the Subcategory should be a short list based on the chosen Category. I know it's not possible to link both columns in Sharepoint.

But is it possible in Powerapps to somehow link the choices in the subcategory to the Category column when filling in a new row ?

Category                                                                                             Subcategory

A	A1
A	A2
B	B1
B	B2

so when I add a new item to my list and choose Category A the only items shown in Subcategory should be A1 and A2.

I've got it to work in a gallery because then I could use distinct based on items in a list but now I create a new row so it doesn't work

[WarrenBelz]

@RvdHeijden ,

This code should work providing the 'Id (ID)' field selected in the gallery is the same record as the Item of the Form.

Patch(
  'FttH Schouwfouten rapportage';
    LookUp(
      'FttH Schouwfouten rapportage';
      'Id (ID)' = Gallery1.Selected.'Id (ID)'
   );
   Form1.Updates
);;
Notify("Wijzigingen zijn succesvol verwerkt";Success);;
ViewForm(Form1)

However as an experiment, simply

SubmitForm(Form1)

and see if that writes the data.

[RvdHeijden]

@WarrenBelz I've got 2 screens in my app and that is 1) to update an existing row and 2) to add a new row.

1. Update excisting row uses the formula form1.updates

When I change the MainProject and Subproject in an excisting row and use Form1.updates the new values aren't send back to SP and the excisting value stay the same in both the app as in the SP list

2. Add a new row uses the SubmitForm(Form1_1)

When I create a new row and fill in all the values and press save everything seems to function (no errors)

But when I open this record the MainProject and Subproject remain Empty and when I check the SP list they are also both empty there

[WarrenBelz]

@RvdHeijden ,

Try OnSuccess of the form you submit

Set(
   gblID,
   Self.LastSubmit.ID
)

then your Patch

Patch(
  'FttH Schouwfouten rapportage';
    {ID: gblID};
   Form1.Updates
);;

I suspect that the item selected in the gallery is not the record you have just created (and this is a more distinct record identification anyway)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[RvdHeijden]

@WarrenBelz ive changed the formula like you said and there is no error but....

When I update the excisting row in order to test it unfortunately it didn't work.
Not only is the row not being updated it is also adding a new row instead of update the excisting row.

I've tried it 4 times (to update the excisting row) but now I have 4 rows in my SP List ;). 

I think there is still an issue with the ID and that is why its adding a new row instead of updating.

but the original problem (not updating the values because they are from a collection instead of the values in the SP list) remains the same

[WarrenBelz]

@RvdHeijden ,

That is exactly why I switched to the ID set as a Variable when you create the new record - to ensure the value gets patched to the correct record. What is the Item of the second form - try

LookUp(
   SPListName,
   ID = gblID
)

Also after submitting the Form, look in Variables for the value of gblID and make sure it is the one you just created. I must admit now I am not a fan of Form.Updates and do not use it. I either SubmitForm or go to the effort of specifying the fields I want to update in a Patch. The few minutes of effort are well worth a process that always works.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[RvdHeijden]

The form to update an excisting record has the the following property for Item

LookUp('FttH Schouwfouten rapportage';'Id (ID)'= Gallery1.Selected.'Id (ID)')

The second form (to create a new item in the SP list) has no value for Item (because the form is to add a new row)

[WarrenBelz]

@RvdHeijden ,

So you are going back and selecting the item from the gallery to edit it ? Is the creation of the new item a process that happens some time before ? 

[RvdHeijden]

@WarrenBelz 

Basically I start by adding a new item/row to my Powerapp and fill in about 90% of the form. When I have more info I search the gallery for the specific item and then I update the excisting row.

I've submitted the form and checked Variables but it doesn't display a value just the text No value or am I looking wrong ?

[WarrenBelz]

@RvdHeijden ,

OK - why are you using Form.updates on the second form - why not simply submit it ? To be honest I always use the same form both new and edit (and view if necessary) and submit both times (and all of these issues simply go away). Also what is the 'Id (ID)' field - SharePoint has an ID field as the unique identifier. you can also use gblID as the standard identifier, so Gallery OnSelect

Set(
   gblID,
   ThisItem.ID
)

then the Form Item as I posted.

LookUp(
   SPListName,
   ID = gblID
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[WarrenBelz]

@RvdHeijden ,

I will be offline now overnight due to time zone