Power FX Formula columns in Dataverse

[Chriscarter]

Hello guys
Please help in dataverse formular

I have a dataverse table that contains information like :
initial amount, entry amount, expense amount and final amount

PS : The final amount = Initial amount + Entry amount – Expense amount

I have already written the final amount formula in the calculated column of dataverse

Every day there is an initial amount

So , Today's initial amount must be equal  to yesterday final amount

My difficulty is to be able to automate the display of the initial amount each day
I would like the value of today's initial amount to automatically be the same value of yesterday's final amount
Anyone have an idea how to do it?

[dpoggemann]

Hi @Chriscarter ,

Couple thoughts / options here...

1. Create a Power Automate flow that will look at yesterday's record and create a new record for next day with the initial amount at the "Final Amount" from prior day.  You could then schedule this nightly to create the new record and populate it.
2. If you wanted to create the new record on demand you could create a classic realtime workflow that would create the new record based on changing a value on the current record like "Create tomorrow's record" and when this field is set to true it would create the next day's record in the workflow setting initial value to the final amount value from the record you are on.

[Chriscarter]

Hello @dpoggemann 

Thanks for your answer

But

I don't really understand the second option and I can't materialize it as you say. please can you explain it in detail and show a concrete example of the rules (how I can do it)

Thanks

[dpoggemann · Accepted solution]

Hi @Chriscarter ,

Here are the details...  I tested out and it worked great.  

1. Create Workflow (you will find in the "New --> Processes" section and it is called Workflow.  Note, when creating these it is the classic interface and will be slow to author.
2. When creating this some key settings you will want is to:
   1. Set the scope to Organization level so it will kick off for any user
   2. Verify you uncheck the "Record is created" checkbox
   3. Select the Record Field Change checkbox and then Select your field to kickoff the workflow.  In my example I actually did it on the Status Reason column on the table and then when the current record is changed to a status reason of "Completed" it will create the new record (this if check is in the action steps section.
3. Setup the steps for the workflow
   1. My first step is a Condition that will see if the status reason is Completed and if it is then it will continue
   2. The next step calls the action to Create a Record and I selected the same table (Test1) in my case
   3. Set the Properties for the new record, see my screen shot
      1. I set the Date to one day after the current date on the record
      2. I set the Initial Amount on the new record to the value of the current record's Final Amount
      3. Set the other values to $0.00 for the Expense and the Entry amounts
      4. Set the Name to the Date - Entry (although I set to current date so this wasn't exactly right :). You can adjust to set the name to whatever you want...
4. Activated the Workflow

Following are screen shots of all the setup:

I tested it and it worked as expected when I changed the status reason to Completed on the current record it created the new record for me.

Some thoughts for when you do this to even make it better:

1. Setup another field on the table that is called "Next Day Created" and have this be a "Yes/No" field that is behind the scenes.  
2. Add a step after creating the new record to do an "Update Record" and update the current record to set this field to "Yes".
3. Change the initial condition to create new records to be an "AND" with the current check on the status reason (or whatever field you are using) and secondly that the value of the "Next Day Created" field is "No".  This way you won't duplicate records if someone happens to change the Status Reason back to Active and then to Completed again.

[dpoggemann]

@Chriscarter ,

To launch the workflow interface.

1. Step 1, navigate into your solution and select the navigation to create a new workflow
   
2. Enter the table for your workflow and make sure to "uncheck" to run in the background and select "Create"
   
3. This will launch a new window (watch your browser popup blocker) that will be in the classic interface for the workflow to complete the steps.  Note, again this may take a couple minutes to launch.  

[Chriscarter]

Hello @dpoggemann 

Thank you very much for everything

Sorry for my silence.

I tried to reproduce what you showed me, but I was stumbled on another level

Let me explain my project a bit more in detail:

In my project, I am trying to set up a cash management application in contact with other departments

In the checkout application, I have 2 main forms, each of which has its own table

The data entry table and the data summary table

Also, in parallel, I am developing a canvas application on power apps

Here's how I see things:

On a daily basis, the cashier will enter incoming and outgoing transactions in a form that is connected to the data entry table

The summary table, on the other hand, contains only cumulated and calculated data which depend on the data of the data entry table

I have already created the 2 tables

I linked the 2 tables

I created the 2 forms on power apps

For the data entry table, here are the columns:

Customer's surname and first names, type of customer, type of transaction (Cash out, Cash in), amount, commission generated, observation

For the summary table, I have several columns such as: Total cash out initial balance, total cash in, total commission, final balance

To create total cash out in the summary table, I configured the condition:

{ if transaction in the data entry table is equal to Cash out,

Then (aggregate): SUM the amount}

So I would like the summary table to contain only 1 single line since these are only cumulative or calculated values. The values ​​will change depending on the selected date

My difficulty:

When I do a test; that's to say :

In my power apps application, when I enter data in the form, the record is created in the data entry table (dataverse), But no record is created in the summary table

I was thinking that since the tables are linked and that the validation of the data entry form contains data related to the summary table, then this should automatically create a record in the summary table. But this is not the case. Do you have any idea how to proceed please?

Looking forward to reading you very soon🤝

[dpoggemann]

Hi @Chriscarter ,

There is nothing by default that will build the summary rows in your other table with Dataverse out-of-the-box. 

Personally I would not recommend creating this summary table to track the totals by date and utilize Power BI do do this instead.  Power BI can connect to your Dataverse environment real time with (Direct Query) and you could easily built the type of dashboards you are looking for here where you could look at totals by date, by type of customer, by customer, etc. based on your transaction data.   The only challenge with this approach would be licensing as anyone that would use the Power BI dashboard would require a license...

If you had to go without using the Power BI dashboard and wanted to still create and manage these summary records in the table then I think you would need to do something through Power Automate or a custom action within Workflow.

High Level Power Automate Thoughts:

Question:  Do you allow updates to your transaction records?  If so, you would need a process that could update the amount on the record and be able to change and back out the prior value. Example, if they said "Cash In", "$50.00" and the updated that record to "Cash Out" "$50.00" you would need to update your summary table to back out the original $50 and then subtract another $50.

Assuming updates can be made to records (as mentioned above):

1. Add "prior_amount", "prior_type", "update_processed", "prior_date", "summarized" as fields to the detail transaction table, these will keep the prior values for the type, date and amount and keep track if the prior update has been processed because you can't have someone try to update the record two times for these fields to different values if it hasn't been processed or you will lose track of the appropriate summary values.
2. Real time workflow on detail table triggered on create or update of the (amount, type, date) fields
   1. If create of record (should be able to check if the created and updated time are same)
      1. set the update processed field to Yes
   2. If update of record and update processed = No and prior fields do not equal current field values
      
      1. Cancel the workflow, set error that the prior update needs to be processed first return back to user (see https://jukkaniiranen.com/2013/11/using-real-time-workflows-to-show-error-messages/) 
   3. else if update and update processed = Yes
      1. set update processed = No
3. Create a Power Automate Flow that will trigger on creation or update of the detail transaction record
   1. Make sure to setup the Power Automate Flow that only one instance can run at a time
   2. On Create of the record then search for the Summary record by Date and if it does not exist then create it and set the values of the summary amounts to the amounts from the detail record appropriately (based on cash in vs. cash out).  Set the Update Processed field on the detail transaction record to "Yes" and set the prior value fields on this record to the values on the record.  
   3. On Update of the detail transaction record (only trigger on updates to the amount, date, type) and Update Processed = No then you would need to find the Summary record for the current date on the record and apply the update either adding or subtracting from the date based on the transaction record.  Next you need to look at the prior fields and apply the reverse update for these fields.  This might require you to find a different summary record because the prior values might have been for a different date if they updated the date field on the record or it could be the same date.  Set the Update Processed on the record to Yes.

This process could be tricky to accomplish as you can see.  Hopefully I am not missing something but I think in concept this would work.  Overall I would utilize the Power BI side as it would be a lot more flexible and doesn't require all this work.  Example, if you now wanted to summarize by another field like Customer Type it gets even more complicated! 

[Chriscarter]

wow

THANKS @dpoggemann 

To be honest, I'm a bit lost in your explanation. Probably because I'm new to the power platform

But I would like to make some comments following your explanations.

First of all

You talked a lot about Power Bi. I would like to remind you that in my statement I did not talk about power BI but about power apps (canvas application)

So I'm not trying to fill the cumulated column with power bi , no

But by filling out a form on power apps

Secondly

Your solution is really complex

But I remain convinced that what I ask is quite possible but surely I do not proceed well

Because it is a question of creating 2 tables, the connected ones, creates the cumulated columns then automatically fills the cumulation column following the actions linked to this column coming from the other table

It will be simpler and more convenient

You can try to do a simulation, create the tables, make the relationships, create the forms...

And see if it works

Surely I'm not doing well, or I miss doing something

THANKS

[dpoggemann]

Hi @Chriscarter ,

I don't think what you are trying to do is possible without the complex portions to keep the summary data up-to-date...  

I would be happy to do a call with you if you would like to walk through this and how Power BI could really simplify this for you if you would like. I will send you a direct message with my availability link...