Calculated total price

[leonyouv]

Hello everyone,

I hope to find an answer to my problem as you are my last chance🙏 , as I haven't been able to find a solution for days.

My problem is as follows: I have created an application with the following parameters: 3 sheets

Sheet 1: Customer database

Sheet 2: Price list per task

Sheet 3: Daily financial data

In Sheet 3, I have created a virtual column to calculate the total sum of prices for each client. Here is the formula I have used:

sum(select(data[Task Price],and([ID Client]=[_THISROW].[ID Client],[Task]=[_THISROW].[Task],[Location]=[_THISROW].[Location])))

This formula calculates the total prices of tasks, but what I want is to find a way to calculate the total sum for a client based on Sheet 2, conditioning it on the fact that if a task is performed for the same location and the same client, the formula calculates only that task once and not every time it appears. For example:

I hope my explanations are understandable. Feel free to ask me questions if I haven't been very clear or if there are any areas of confusion.

thank you all in advance for your supports.

[TeeSee1 · Accepted solution]

You first create a flag that identifies the first occurrence of data per [client],[service],[location] combination.

[_RowNumber] 
= 
MIN(
 "data",
 "_RowNumber",
 AND(
  [ID Client]=[_THISROW].[ID Client],
  [Task]=[_THISROW].[Task],
  [Location]=[_THISROW].[Location]
 )
)

Then SUM the task prices over them by

SUM(
 SELECT(
  data[task price],
  AND(
   [ID Client] = [_THISROW].[ID Client],
   [FLAG] //a column created using the above expression
  )
 )
)

[TeeSee1 · Accepted solution]

My mistake. Should be this

[_RowNumber] 
= 
MIN(
 SELECT(
 data[_RowNumber],
 AND(
  [ID Client]=[_THISROW].[ID Client],
  [Task]=[_THISROW].[Task],
  [Location]=[_THISROW].[Location]
 )
)

[WillowMobileSys]

You need take the current row Task Price and add all the other rows that DO NOT matchfor the Client.  Something like:

[Task Price] +

SUM(SELECT(data[Task Price], 
           AND([Client] = [_THISROW].[Client],
               OR([Task] <> [_THISROW].[Task],
                  [Location] <> [_THISROW].[Location]
               )
           )
))

[leonyouv]

I want to thank you very much for your help and the attention you're giving to my problem. However, I've executed the formula you provided, as you can see.

The result obtained is as follows:

As you can see, the values I have are not what I expected. Please let me know if you need more details or if I have made a mistake somewhere."

[TeeSee1 · Accepted solution]

You first create a flag that identifies the first occurrence of data per [client],[service],[location] combination.

[_RowNumber] 
= 
MIN(
 "data",
 "_RowNumber",
 AND(
  [ID Client]=[_THISROW].[ID Client],
  [Task]=[_THISROW].[Task],
  [Location]=[_THISROW].[Location]
 )
)

Then SUM the task prices over them by

SUM(
 SELECT(
  data[task price],
  AND(
   [ID Client] = [_THISROW].[ID Client],
   [FLAG] //a column created using the above expression
  )
 )
)

[leonyouv]

hi Mr.

Thank you for your response.

I attempted to implement the formula you provided,

but I'm struggling to fully comprehend your instructions. Could you please clarify this for me ? 

MIN(
 "data",
 "_RowNumber",

I'm relatively new to AppSheet and would appreciate your guidance.

thank you for the attention you're giving to my problem.

[TeeSee1 · Accepted solution]

My mistake. Should be this

[_RowNumber] 
= 
MIN(
 SELECT(
 data[_RowNumber],
 AND(
  [ID Client]=[_THISROW].[ID Client],
  [Task]=[_THISROW].[Task],
  [Location]=[_THISROW].[Location]
 )
)

[leonyouv]

Wow, it's incredible! Everything is working perfectly now. You really saved me. Thank you so much for your help; I truly appreciate it. You're Awesome!

[WillowMobileSys]

I see I didn't account for repeats in the OTHER task rows.  I didn't understand that was a possibility from the original example.  Sorry for the confusion.

So...A few things:
1)  Why are there repeated entries for the same Task/Location?  If you are not including them in the Pricing then that implies they are not really needed.  If that is true, then it is MUCH better to prevent entry of them in the first place.  You can do that with validation on the entry Form. 

If you continue to allow duplicate entries that are not needed, you will find yourself working around those rows in EVERYTHING you do related to Task entries.  Preventing them up front will eliminate the need for complicated expressions and you will be able apply a simple Sum across ALL entered rows.  Problem solved.

If you DO need the duplicate entries, then read on...

2)  The proposed solution by @TeeSee1 will work, as long as you do not allow deletion of Task rows ...OR... implement the flag as a Virtual Column. 

[_RowNumber] is assigned when data is loaded, in the order the rows are loaded.  If you delete one, then re-load, the rows will be renumbered changing the number value from the deleted row all the way down through the list.  If you have saved the "flag" value in a table column, they do not update automatically and you will end up with incorrectly flagged rows.

To void this, you can implement the "flag" value as a Virtual Column, this means the flagged values are recalculate after every re-load/Sync of data.  HOWEVER, this re-calc will happen on EVERY row on EVERY Sync.  As the Task list grows, this re-calc will get slower and slower over time.

3)  I strongly recommend AGAINST using [_RowNumber]...EVER.  DEFINITELY do NOT use it as a table key.   There is not a guarantee what order the rows will be loaded into the app. 

As I understand it, [_RowNumber] was originally a way to auto-assign table keys as Sheet rows were entered.  But there were issues (more than indicated above) and AppSheet introduced the UNIQUEID() function to assign unique key values.  The [_RowNumber] column continued as a default table key IF no key column was defined.  As such many apps, unfortunately, do continue use [_RowNumber] making it hard for AppSheet to deprecate this column.  

IF you ever transition your app to a database, and have not taken extreme care in how you use the [_RowNumber] column, you WILL experience tremendous difficulty in unraveling the use of that column.


@TeeSee1   

[TeeSee1]

@WillowMobileSys is absolutely correct.

@leonyouv , please pay attention to what he advises!

[leonyouv]

Hello Mr. @WillowMobileSys

Firstly, I want to thank you for your superb work and the time you dedicate to helping beginners like myself. I've carefully read your advice, and indeed, there are many things to correct and improve in my program. In fact, my basic database architecture likely needs a complete rebuild.

To address your question regarding the repeated entries, let me explain my initial reasoning during the design phase. I intended to create three Sheet:

• Sheet-1 :Client database,
• Sheet-2 :Pricing database, and
• Sheet-3 :Database for daily pricing transactions.

In the third table, I aimed to only insert daily payments from my clients based on the "Task" type and "Location," while looking up the "task price" from the  Sheet-2. Additionally, I considered that my clients may don't pay the entire "task price" at once but by part until the completion of the work.

However, the issue I encountered is that the proposed prices is (somme time) not FIX may vary from one client to another. For example, I might charge client 1 $50, $60, or $70 for the "Task Cleaning," depending on various parameters defined in my quote. So, I decide to add the "Task price" column with an initial value that I retrieve from Table 2 alongside each entry. This allows me the flexibility to modify it as needed. If there's a better way to approach this, I'm completely open to restructuring my program.

Regarding the [_RowNumber], you're absolutely right, and I've implemented a new column using the UNIQUEID() function in my Table 3.

As for the "flag" column, I had already inserted it as a virtual column. I understand that with more data rows, the calculation time will increase. Unfortunately, at the moment, it's the only solution I've found. If you have a better suggestion, I'm all ears.

Thank you once again for your attention to my problem i really appreciate and grateful for your help .

[WillowMobileSys · Accepted solution]

I still don't understand WHY there are multiple rows for the same Client, Task, Location and, I presume, Date.

I do understand the need to have varying Pricing per Client.  You probably want to build that into your tables so that when you enter Tasks and look up the Price,  you are getting exactly the Price you want.  There are 2 ways to do this:

For both, add a Client column to your Price table, then either:

1)  Insert all your DEFAULT/NORMAL Pricing with NO client inserted.  Then Insert any Client specific Pricing in the same table, obviously with the Client assigned. The idea is to FIRST lookup a Price for a Task and Client.  If no row is found then grab the DEFAULT Price.  The advantage here is that if most Clients are subject to the DEFAULT Price, you can manage the Price for all of them with a single update in your Price table. Then you have only a few rows that need special attention.  The disadvantage is that for most Clients, there is a 2-step lookup to get the Price but it is a very small Price to pay for convenience of maintenance.

2)  Simply enter all tasks for all Clients.  Initially assign the DEFAULT Price and then adjust the Tasks for Clients with special Pricing.  The benefit is you will always find the correct Price on the first Lookup.  The downside is if you need to adjust Pricing, you will need to touch EVERY Client row impacted by that Price change.  If you have a lot of clients, that can be a lot of work.

So, if MOST Clients use your DEFAULT Pricing, use 1). Otherwise use 2).

I hope this helps!