Formula for dynamic title not working & filtering formula not submitting to the list

[Anonymous]

Hi folks,

I've watched a whole bunch of videos to educate myself on powerapps to customize sharepoint forms and while I've made some progress, I am getting sooo frustrated on little things I thought I'd come here and ask 🙂 If any of you have solutions for the below, that will be fantastic 🙂

• I am trying to have the title of a DataCardKey change based on the selection of a DataCardValue. I have the following formula which keeps returning invalid argumen type: If(DataCardValue9 ="Hourly","Job Grade",DataCardValue9 = "Salary","Job Band","Job Band / Grade")
• I have a DataCardValue for which the selection is filtered by another card selection. This functionality works very well but when I press save, the selected field does not load the value against the record.
• I have set a few fields to only appear when in edit mode, not new. When I click on "new item" to add a record, the correct version of the form appears. After filling out the details and pressing save, a record is added to the list but the form does not close and now displays all fields. I would just like for the form to close upon saving but can't figure out what I've done wrong
• Can anyone recommend a video or some reading so I can better understand the different controls like OnSelect, OnFailure etc and what they relate to?

I have other things that are very annoying but related to sharepoint so will seek help somewhere else.

Thanks in advance to anybody who can assist.

OF

[RandyHayes]

@Anonymous 

Don't get too frustrated.  Feel free to reach out here any time. It is sometimes a challenge getting the swing of things though, so, let's look over your issues:

---

I am trying to have the title of a DataCardKey change based on the selection of a DataCardValue. I have the following formula which keeps returning invalid argumen type: If(DataCardValue9 ="Hourly","Job Grade",DataCardValue9 = "Salary","Job Band","Job Band / Grade")

You're close on this, but you need to specify which property of the DataCardValue that you want.  If it is a text control, then it's usually pretty simple...just use the Text property.  If it's a ComboBox or other "data bound" control, then it can get a little trickier as you just need to understand what data you have bound to the control and what structure it has.  

So, in the above, if DataCardValue9 is a text control, then your formula would be this:

If(DataCardValue9.Text ="Hourly","Job Grade",DataCardValue9.Text = "Salary","Job Band","Job Band / Grade")

If it is a ComboBox or DropDown control, then, like I said, it can get trickier, but we'll start with the most common solution:

If(DataCardValue9.Value ="Hourly","Job Grade",DataCardValue9.Value = "Salary","Job Band","Job Band / Grade")

So, the point here, is that you were trying to compare a Control with a text value...which is an invalid argument.

I have a DataCardValue for which the selection is filtered by another card selection. This functionality works very well but when I press save, the selected field does not load the value against the record.

Need a little more detail on this one...Are you stating that the value is not getting saved in the datasource or that your value is not appearing on the form when you view or edit the record in the form?  What will be important to know here is what is your datasource, what kind of field/column is it, what is the items formula on the control, what kind of control is it, and what formulas do you have for Default and Update?

I have set a few fields to only appear when in edit mode, not new. When I click on "new item" to add a record, the correct version of the form appears. After filling out the details and pressing save, a record is added to the list but the form does not close and now displays all fields. I would just like for the form to close upon saving but can't figure out what I've done wrong

Is this a SharePointIntegration that you are doing or an app?  If it's a SharePointIntegration, then don't forget to put a RequestHide() function in your OnSuccess action of the SharePointIntegration object.  If you don't then SharePoint will not know to hide that form when completed.

Can anyone recommend a video or some reading so I can better understand the different controls like OnSelect, OnFailure etc and what they relate to?

Definitely read over the documentation for SharePointIntegration.  This has tons of information in it that will guide you through those Actions.  Also, I would take a look through the Control documentation just to be familiar with all the Controls available to you and what they do.  If you want more details on any of them, take a look at Shane Young's library of videos and/or ask additional questions on this forum.

I hope all of this addresses some of your questions and is helpful for you.

[Drrickryp]

Hi @Anonymous 

I think I can help with your questions.  

1. Your If() statement is incorrect.  First, DataCardValue9 is the name of the control not the text inside it.  To reference the Text, you need to have DataCardValue9.Text= "Hourly" as the conditional part of the If() function.  I am not sure about what you are attempting to do but I suspect that the logic is something like  this:  If the text in the DataCard is Hourly, then the Key value should read Job Grade, If the text in the card is Salary, then the Key should read Job Band or else if the text is neither Hourly or Salary, then the key should read Job Band/Grade.  The default value of  DataCardKeys is Parent.Default.  Replace that with 

   If(DataCardValue9.Text = "Hourly", "Job Grade", DataCardValue9="Salary","Job Band","Band/Grade")

   See canvas-apps/functions/function-if
2. Check your Update property of the card to make sure that it is saving the correct value. Make sure that this value is acceptable to SharePoint. A common problem is that the SharePoint list item is a choice or lookup type.  If it is a choice type, change it to Single line of text. If it is a lookup, the problem is more difficult to solve.  I don't use Lookup type in my Sharepoint lists but use a separate list and import it into my apps and do the lookup in Powerapps. 
3. To close the form after the data is entered, go to the OnSuccess property of the form and put the function Back().  That way, if the data is successfully entered, the form will close and you will return to the screen that you came from.  

OnSelect, OnSuccess and OnFailure are not controls but they are properties of the control.  These are Action functions that tell PowerApps what to do when certain events occur, such as pushing a button, icon or label (OnSelect), or when the data in a form is successfully saved (OnSuccess) or when the data cannot be saved because of some error (OnFailure).  Using the OnSuccess property of the form in your example above, putting Back() will cause PowerApps to Navigate back to the screen that preceded the one with the form.  As an alternative,  you could put Navigate(SomeOtherScreen, Fade) in the OnSuccess property to move to another screen to either view your data (like a screen with a gallery on it, or another Form to fill out more information). 

[Anonymous]

@RandyHayesand @Drrickryp , thank you very much for spending the time to educate me.

So I have tried what you both suggested and unfortunately didn't make much progress:

. on the dynamic DataCardKey, well note your recommendation. THe problem I have is therefore related to the fact that the origin field (the one determining the next DataCardKey label) is based on a drop down, and you both agreed that it would make life more complicated. I've learnt to pick my battles, so I won't make the title dynamic 🙂

. On my cascading drop downs, the values appear clearly in the list, I can select them and "save the form" without any problems. When I however edit the form to review the entry, the fields related to my cascading drop downs are blank. For info, the fields are looking up a value in a separate sharepoint lookup table and I then filter them in the Items section. Example for one of the fields: Filter('Site Area',Title=DataCardValue3.Selected.Value).

I probably only have 50 to 100 values so if I needed to store them somewhere else I could, I just wouldn't know what to do.

. now the part where I'll sound even silier, how do I add a function (RequestHide or Back) to the OnSuccess? At the moment I have: Collect(Collection1, VacancyTracker.Current) and can't figure out what to do.

Thanks again for taking time to respond.

OF

[RandyHayes]

@Anonymous 

Let's chip away at it...

. on the dynamic DataCardKey, well note your recommendation. THe problem I have is therefore related to the fact that the origin field (the one determining the next DataCardKey label) is based on a drop down, and you both agreed that it would make life more complicated. I've learnt to pick my battles, so I won't make the title dynamic 🙂

You were actually pretty close on that one. If you can supply the formula you use for your Item property of the dropdown, we can probably nail that one down easily.  Most likely your formula will be this (and sorry, I had a typo in my first formula to you about this):

If(DataCardValue9.Selected.Value ="Hourly","Job Grade",
   DataCardValue9.Selected.Value = "Salary","Job Band",
   "Job Band / Grade")

. On my cascading drop downs, the values appear clearly in the list, I can select them and "save the form" without any problems. When I however edit the form to review the entry, the fields related to my cascading drop downs are blank. For info, the fields are looking up a value in a separate sharepoint lookup table and I then filter them in the Items section. Example for one of the fields: Filter('Site Area',Title=DataCardValue3.Selected.Value).

I probably only have 50 to 100 values so if I needed to store them somewhere else I could, I just wouldn't know what to do.

This is not a problem to resolve.  You're stating that your Items property for the combobox is Filter('Site Area', Title=DataCardValue3.Selected.Value)  This is all fine.  I assume 'Site Area' is the table/list you are looking up values from.  But still....

Need a little more detail on this one...Are you stating that the value is not getting saved in the datasource or that your value is not appearing on the form when you view or edit the record in the form? This question is answered - your value is not appearing when you Edit. What will be important to know here is what is your datasource (assuming SharePoint from all we've talked about in this posting), what kind of field/column is it (know now that it is a Lookup column), what is the items formula on the control (This we know now above), what kind of control is it (pretty sure it is a ComboBox control), and what formulas do you have for Default and Update?

Need the above blue answer and also verify all the assumptions in red.

---

. now the part where I'll sound even silier, how do I add a function (RequestHide or Back) to the OnSuccess? At the moment I have: Collect(Collection1, VacancyTracker.Current) and can't figure out what to do.

This is no worry...you will find the SharePointIntegration control in the left tree view of your designer.  If you click on it, then you can choose the actions in the formula bar.  By default, the OnSuccess should have the RequestHide() function in it...but, let's make sure.

See photo below...in this case we see that the SharePointIntegration is selected.  In the Formula Bar where we see DataSource, drop that list down and you will see all the other actions...including OnSuccess.

[Anonymous]

I feel like I'm playing golf. Having a shocker of a day trying to do any of this, just about to give up and then onle little thing works and it keeps me trying 🙂

THanks very much for your help @RandyHayes 

Your solution for the dynamic datacardvalue is spot on 🙂

Your solution for the RequestHide() works as well. My problem was I could not figure out how to write the syntax but realized that I could just go with a simple "SubmitForm(VacancyTracker) && RequestHide()" on the OnSave function as I couldn't find the OnSuccess at all.

With regards to the cascading drown down:

. main sharepoint list (Vacancy Tracking) contains a column named "OC / Area" of type lookup against another list named "Site Area" in the column "Area"

. secondary / lookup sharepoint list (Site Area) contains two columns of type single line of text. One column contains the overall location (LocationID) and the other "Area" contains what is being filtered by the powerapps form.

The OC / Area field in the powerapps form is in fact a combo box and has the properties in the attached screenshot

[RandyHayes]

@Anonymous 

Excellent!  Sounds like progress!

So, actually for your formula with OnSuccess...my bad.  I was confusing with the SharePointIntegration object and your Form.  What I really was thinking but explained poorly was that you need (or should have) the RequestHide() function in the OnSuccess of the Form, not the SharePointIntegration.  

So, OnSave happens when a user clicks Save.  There you should have the SubmitForm(VacancyTracker).  Now, on the OnSuccess of the VacancyTracker form, you should have the RequestHide() function.  This is actually a bit important as the OnSave will submit the form and then hide it as you have it now.  If there is an error, you don't want to hide the form.  So, putting it in the OnSuccess of the Form will prevent that from happening.  In this way, a user will click Save - that will fire the OnSave action (SubmitForm).  If the Submit is successful, then the OnSuccess will fire, which will request the form to be hidden.  If it fails, then OnSuccess will not fire and the user will still see the form any any errors that need correction.  Sorry for the confusion.

As for the DropDown...

Here are the questions:

1) What type of column is the Area/OC field in your SharePoint list?  Just to verify...you mentioned Lookup, but just making sure it is defined as a Lookup column in SharePoint and that it is using the List 'Site Area' as its source.

2) From the picture -  Are you getting values in your ComboBox (DataCardValue11)?  If not, then we might want to look at the filter in the Items property a little closer. You mentioned the 'Site Area' list has two columns - LocationID and Area.  Yet, in your formula you are using Title as the filter.  Perhaps you want the Area column there instead of the Title column?

3)  I believe you will want this to have the proper value during an Edit and nothing during a New form.  As well, you want to make sure you have the proper Update property.  So, for this, we need to look at the DataCard parameters for this field.  So, perhaps click on the DataCard itself and take a screenshot of the Properties for that (to include, Default, and Update properties).

[Anonymous]

@RandyHayes, thanks again for the follow-up.

So, on the OnSave vs OnSuccess topic, I've done the changes you suggested (haivng the RequestHide() on the OnSuccess) and unfortunately I'm back to square one, where the form does not disappear and the fields that are only supposed to appear in edit mode suddenly appear (while the records are being added to the list). So I think I'll go back to the previous scenario which seemed to work ok 🙂

Wiht regards to the cascading lists and your clarification questions:

1) yes it a as a lookup column as per attached screenshot

2) yes I am getting values appearing in the drop down and I can even select them to be loaded in the cell. THey just don't get added to the record when I press save (or when I try to edit an existing record). THe reference to Title is I believe due to the fact that I've relabelled the default "Title" column of the list and somehow PowerApps didn't pickup the change, nevertheless this part works fine

3) I have attached another screenshot. Now I don't know if that would solve the issue, but someone mentioned on this thread that I should probably host the values somewhere else, outside of sharepoint. I have no problem doing this at all if I can be pointed towards the right way of doing it and how I can still have the value added to my list record upon creation of each form.

Thanks,

OF

[RandyHayes]

@Anonymous 

Okay, let's chew on a few of these:

So, on the OnSave vs OnSuccess topic, I've done the changes you suggested (haivng the RequestHide() on the OnSuccess) and unfortunately I'm back to square one, where the form does not disappear and the fields that are only supposed to appear in edit mode suddenly appear (while the records are being added to the list). So I think I'll go back to the previous scenario which seemed to work ok 🙂

Seems like the logical step, but there is an issue.  The RequestHide() should work from the OnSuccess, not from the OnSave.  This can lead you to problems down the road if there are issue with a record.  If you have RequestHide() in your OnSuccess action of the Form - and it is not happening, then something is not successful.  This might warrant more investigation.  To summarize, you should have NewForm(VacancyTracker) in your OnNew action of the SharePointIntegration and you should have SubmitForm(VacancyTracker) in your OnSave action of the SharePointIntegration and you should have RequestHide() in your OnSuccess action of your VacancyTracker form.  This is the proper setup.

With regards to the cascading lists and your clarification questions:

1) yes it a as a lookup column as per attached screenshot - no problem

2) yes I am getting values appearing in the drop down and I can even select them to be loaded in the cell. THey just don't get added to the record when I press save (or when I try to edit an existing record). THe reference to Title is I believe due to the fact that I've relabelled the default "Title" column of the list and somehow PowerApps didn't pickup the change, nevertheless this part works fine - no problem

3) I have attached another screenshot. Now I don't know if that would solve the issue, but someone mentioned on this thread that I should probably host the values somewhere else, outside of sharepoint. I have no problem doing this at all if I can be pointed towards the right way of doing it and how I can still have the value added to my list record upon creation of each form. - no need, this is fairly easy to make work, it's just not something that "here use this formula" will solve in an initial posting response. It involves a few steps of investigation to understand what you are working with in your data, then a "here use this formula" will work...

---

Here, use this formula: lol

Actually, let's make this change.  On the 'PM Required_DataCard1' change the Update formula to this:

{
  Id: DataCardValue11.Selected.ID,
  Value: DataCardValue11.Selected.Title,
  '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
}

In order to update a Lookup column like you have, you need to supply a record to it, and that record needs to have an Id and a Value, and it needs to specify the proper @odata.type.  The above will do that.

When you change the Items property of the combobox in your datacard, this is always something to keep in mind...you lose the proper record needed to update the Lookup column.  The above formula will rebuild that record.

Leave the rest as you have it.

   Default:  ThisItem.'OC / AREA'

And, leave the datacardvalue11 control as you have it.

   Items:  Filter('Site Area', Title=DataCardValue3.Selected.Value)

This should do what you've been looking for.  Put it in place and give it a try.  Let me know if that is joy or sadness.

[Anonymous]

@RandyHayes , you're a star 🙂 It worked so thank you very very much

With regards to the OnSuccess problem, here is what I have, which unfortunatel returns the following error: Invalid argument type. Expecting one of the following: boolean, number, text.

Collect(Collection1, VacancyTracker.Current) && RequestHide()