Unanswered

How to store in (CDS) Dataverse field the sum of 4 other fields with currency type?

(0) Share

Report

Posted on by Forge

Hi ,

I'm building a form with canvas app. I have a Dataverse table with multiple fields, I need to store the total of 4 fields in a total field. Table fields are in form in edit display mode using variable varFormData to display all form in the app.

Scenario:

Table name : Main table

FieldName1 : Type currency

FieldName2 : Type currency

FieldName3 : Type currency

FieldName4 : Type currency

FieldNameTotal: type currency

I want to store the sum of FieldName1,2,3 and 4 in FieldNameTotal and I want to display the currency symbol ($) in all fields.

I'm using a Text label as a workaround and storing the total field in a collection.

Code I'm using Now is: Text(Value(Sum((DataCardValue25.Text),(DataCardValue27.Text),(DataCardValue29.Text),(DataCardValue31.Text))))

Thanks,

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

codevenkat 126 on at

Like (0)

Report
Copy link

Link copied!

Hi Forge,
You have to remove currency before adding fields , which is clearly explained in the below topic https://powerusers.microsoft.com/t5/Building-Power-Apps/Sum-Currency-Fields-and-Displaying-Currency-as-s/td-p/195927

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Forge,

You can define a calculated column in the Dataverse that will do the sum of these four currency values. I am showing two in the example below but this is a Currency column that is Calculated.
Hope this helps. Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

Was this reply helpful? Yes No
Forge 10 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann ,
I have created 2 calculated fields and used your formula: xxx_FieldName1+xxx_FieldName2+xxx_fieldName3+xxx_FieldName4
I tried to display the fields in a form (set to new mode) but the total fields cards doesn't show anything. The cards are in: Parent.DisplayMode and the text properties of the cards are set to: Text(Parent.Default, "$###.##").
Thanks for your help,

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Forge ,

That field will not be populated until it is patched in the database. If you save the other four fields, it should populate. Please let me know if I am missunderstanding.

Thanks much!

Drew

Was this reply helpful? Yes No
Forge 10 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann ,
I really appreciate your help. This solution is partially good for my app because it stores the sum of the fields but it doesn't give me the ability to show the user in Realtime the total field as they input the data. I need the total field to show the amount in Canadian dollar as the user inputs data.
Thanks,

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (1)

Report
Copy link

Link copied!

@Forge ,

Modify onchange property of all textinput control to UpdateContext({value2:TextInput1.Text}) to get the modified value. Make sure to have unique context variable in updatecontext like value1, value2 etc.

In total text property, use Sum(value1, value2, value3, value4). You will get instant result on change of those values.

To display currency symbol, modify text property of control to "$" & Text(Sum(value1, value2), "[$-en-US]0.00")

Mark it as solution. If it resolves your question.

Was this reply helpful? Yes No
Forge 10 on at

Like (0)

Report
Copy link

Link copied!

Hi ,
The value in the fields are all currency type and will not accept (TextInput1.Text) function it is formatted as a number (TextFormat.Number).

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report
Copy link

Link copied!

@Forge ,

While displaying show $ symbol. While patching remove $ and just pass number.

Regards,
Prakash

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report
Copy link

Link copied!
@Forge

As I mentioned earlier, on change property of datacardvalue should be UpdateContext({value2: DataCardValue8.Text})

Default property of the datacardvalue should be If(IsBlank(value2), "$" & Text(Parent.Default, "0.00"), "$" & Text(value2, "0.00"))

Update property of datacard should remain same, don't make any modification i.e. Value(DataCardValue8.Text). In your case datacard value will be different.

Follow above steps for other currency controls too.

As per MS docs, Value function for string format.
The string may be prefixed with the currency symbol for the current language. The currency symbol is ignored. Currency symbols for other languages are not ignored.
I have validated from my side it is working fine.

Let me know if that works for you.

Mark it as resolved, if it answers your question and cheers too.

Regards,
Prakash

Was this reply helpful? Yes No