Ok so I have a list in Sharepoint and I have calculated columns in Shareponit. They are not showing the data in PowerApps. People keep telling me to do the calculations in PowerApps but I don't know how to do the calculations or how. Can someone please screenshot for me so I can understand? This is my SP list, and what I have in Powerapps so far. So when someone fills out the form, they have a drop down option for to select CAD, EXTREME or STANDARD. And I want it to return back to them that if they select CAD, then it'll be $100. Pleeeeease help 😞
@Anonymous
Yes, so what you mention is perfectly achievable in PowerApps with the method I provided.
I'm not clear on your design or any particular details about your app other than what I have provided, so some of the suggestions are a bit of guess-work based on how I would do it.
The below is not a step-by-step list, but a general list of steps (so some exact details are not here because they rely on other factors of your app that I am not aware of).
If you have a column for the License Type already, you can make that an Allowed Values field in your form and point the dropdown control it will have to be the other new list.
This is easily achieved in the Form. When you add your License Type field to the form (and if you've already added it and unlocked it, you'll need to remove it and add it back), once you've added the field, change its field type to be "Allowed Values".
This will change the control to have a dropdown in it. Change the Items property to be your other List name and have it display the Title of that list (the license type).
Then for your new Cost column, set the Update of that to be the selected cost value from the dropdown.
If you want some sort of Pop-up verification or notice, then this would be done as part of the Submit actions.
So, you mentioned you tried some things and had errors - can you provide more details on what you ran into?
Hey Randy!
Ah see I wish I could do it that way, but my boss wants to have a choice column where the user will pick between CAD, EXTREME and Standard and then when they submit the form, it'll pull up a confirmation page for them that says that they picked either CAD, etc and then what the cost is.
I tried the first formula you mentioned but not sure if I just put it under the wrong property or what, but it didn't work 😞 it just gave me an error message
@Anonymous
No problem!
I just realized I misspoke - you would not be changing the Text property of the DataCardValue that you will have in the column datacard in your form, you would be changing the Default property.
As for a Lookup column - No, I would avoid the lookup column as lookup columns really only serve for the display of information in SharePoint lists. Since your app is the primary display method, then simply use a numeric column for your new cost value.
Then, in your app (in the Text property of what I mentioned before), reference the cost list with a LookUp function.
For example, if you have a List in SharePoint called ItemCosts and then you had a column in that list (beside the Title column you will have by default) called Cost.
You would have in there a record for each of your License types. So a record with a Title of CAD and a cost of 100, then another record with EXTREME as the Title and 200 as the cost, and then a record with STANDARD as the Title and 300 as the cost...then, your formula in the Default property of the control would be:
Text(
Coalesce(
Parent.Default,
LookUp(ItemsCosts, Title = yourLicenseTypeDataCardValueControlName.Text, Cost)
),
"$#,##0.00"
)
This also introduces the Coalesce to return either the already recorded value (if you are editing a record) or the new value as looked up from the other list.
Also... I don't see a Text field?
Hi Randy! Thanks for this, I'm going to try it out! When you say to put the pricing info in another list and reference it, do you mean do a lookup column from one list into another?
@Anonymous
Calculated columns are usable in PowerApps, however, they are read only as it is the role of your SharePoint list to populate them.
This means that if you are entering in a record in your App, it is not submitted until you submit it. During the entry, the calculated column is meaningless. Once the record is submitted, the calculated column would have a value - but not until submitted.
So, from a user entry perspective, it is much better to have your calculations directly in your app.
You could leave the calculated column in place and ignore it in the app and do the calculations for display purposes only - but this would mean you are maintaining formulas in two places.
A better choice would be to have a new Cost column in your list and then in your form, you can add that field and make it non-editable by the user and put a calculation in the Text property of the TextInput control in the datacard that you will have.
A formula such as this in the Text property would give you what you need:
Text(
Switch(yourLicenseTypeDataCardValueControlName.Text,
"CAD", 100,
"EXTREME", 200,
"STANDARD", 300
),
"$#,##0.00"
)
Ultimately, you should possibly consider putting your pricing information in another list and reference the list instead in the formula. This way you can alter pricing and License Types in that list rather than altering your app.
I hope this is helpful for you.
Hello @Anonymous,
You cannot use the Calculated Field Directly in the PowerApps, you need to use the Same Formula which you are using in SharePoint in PowerApps and Push the same to Custom Filed which does not have Formula
Lets wait for others reply too.
Please mark as Answer if it is helpful and provide Kudos
Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com
WarrenBelz
146,771
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,091
Most Valuable Professional