Hi, I'm building out a quoting app, and I'm ...

AppSheet AppSheet Q&A
1

Hi,

I’m building out a quoting app, and I’m having a hard time with getting Appsheet to update a value, based on the sum of other values. For my quoting app, I have a spreadsheet for quotes, and a spreadsheet for two things that go into my quoting items - manufacturing time and materials. Since any item I’m quoting can have multiple materia, I obviously use a reference for that. Same with multiple manufacturing times.

I’m able to create a virtual column that basically does the following…

  1. Add up all the costs of material line items 2. Add up all the costs of manufacturing time line items. 3. Display total cost per item being quoted.

This works great with virtual columns - I have a virtual column named “total_cost”. The moment I add a material, the virtual column updates, which is great.

However, I don’t want to just display the column “total_cost”. I want to save it so I can access this cost from other apps I’m building. I have a column called “saved_cost” with the app formula “=[Total_Cost]”. This works when you first are creating an item and adding materials and manufacturing times, but once you save that item, it locks the saved_cost. I can then add materials, manufacturing times, and delete them, and the virtual column “total_cost” gets updated, but the “saved_cost” column doesn’t.

In order to get the saved cost to update it, I essentially have to click “edit” on a quote, and then save. Because I’m editing it, it updates “saved_cost” with the virtual column value.

Is there a way around this? I’m thinking one of two ways…

  1. When I’m saving a material that I’m adding to a job, have an action that replaces “save” which updates that original quote column with the new cost. 2. Have some sort of “refresh” or “update” button/action that will recalculate the total_cost and copy it to the “saved_cost” column.

Or is there an easier way to do this I’m missing?

Thanks!

2

Hi @Greg_Huston,

As per my understanding, the second option you have listed above of creating an update action button for setting [Saved_Cost} real column with latest in VC [total_cost]) in parent table is workable approach.

This will be however a pure manual operation in that, whenever user adds child table records , after that she/he will need to tap the action in corresponding parent record.

However if the view is arranged such that the related child records are added/updated from the corresponding parent’s detail record, then overall operation will be user friendly.

There could of course be more efficient solutions.

3

Hi @Greg_Huston,Alternatively, an easier approach, if feasible for you,. You may wish to calculate the total cost value in your parent table by using spreadsheet formula just for that real total cost column ( and any similar aggregate column).

This approach will update the total cost in the parent table as soon as user saves the child table record.

Something similar has been done in Order Capture sample app. Please refer to Orders table Total Cost column

appsheet.com - Order Capture - An app for managing customers, products, and orders. Order Capture - An app for managing customers, products, and orders. appsheet.com

4

Check this sample app appsheet.com - EventAction - Created by Aleksi EventAction - Created by Aleksi appsheet.com