How to reroute the data that is being saved in the app

[cainsworth12]

The app I built is pulling from a google sheet. Whenever I save data in the app it modifies the original data in the google sheet. Instead I want it to save that entered data to a different sheet.

Is this possible?

Thank you

[LeventK]

@cainsworth12
Can you elaborate your query a bit more?

[cainsworth12]

The data from the google sheet that is being used to create the app is being changed every time we enter in any new info in the app.

I want that data to be captured and deposited on a different sheet. I do not want the data to be changed on the original sheet.

[LeventK]

Is the table/sheet that you had used to structure the app and the table/sheet that you want you’re data to be recorded structurally identical or different? For sure it’s possible if you can create a second sheet for the data to be recorded, revert your first table to Read-Only and use it as a resource for the initial values of your second table’s data entry or Form View.

[cainsworth12]

Yes they are structured the same.

How to do I make the read only change on the original table?

[LeventK]

@cainsworth12

[cainsworth12]

Just tried that and now we can’t input any data at all in the app. I need to enter the data and just not have it record back to the source.

[LeventK]

@cainsworth12
You need to create a copy of your 1st sheet and import this copy (2nd sheet) to your app with required access privileges. If I have understood you correctly, all you need is to call data from the 1st table, make some edits to the data if necessary and save this data to the 2nd sheet. To achieve this, you also need to create a Behaviour Action with a LINKTOFORM deep-link, which will read and fill the Form View of your 2nd sheet with the data from the 1st sheet. 1st sheet will remain as Read-Only and 2nd sheet will be all editable.

help.appsheet.com

LINKTOFORM()

Constructed deep link to form

[LeventK]

@cainsworth12
Another approach could be using the REFs. With this method, you won’t need to show the 1st table in a View either. After you have created the copy of your 1st sheet, just add an additional column in the 2nd sheet for referencing the 1st table. When you import this table to your app, set this columns type to REF and choose your 1st table as the parent record. Than you can set de-ref expressions for each one of the remaining fields Initial Value in the 2nd sheet’s form:

[RefTypeColumnName].[ColumnNameYouWantToReadItsValueFromParent]

help.appsheet.com

Dereference Expressions

Use a dereference expression to retrieve a field value from a referenced record.

When you use REF type columns, by-default the ref dropdown will populate the values from the column which is marked as Label. Provided you think that the column marked as label is not well-enough explanatory for the user about which record they are selecting, you can create a Virtual Column and create your own label with a CONCATENATE expression as well and mark this virtual column as your label column.

help.appsheet.com

Row Labels

How to determine which columns are prominently displayed in a view.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

help.appsheet.com

CONCATENATE()

Merged text from parts

[pravse]

So @cainsworth12 if I may clarify (and Levent is probably two steps ahead of me on this): do you want to find a row (from the read-only table) and then decide to edit it in the app, and when you save, you want a new row added to a different table?

If so, tLevent’s suggestion is a reasonable way to do it (read-only table, a second writeable table, and an action to link the two).

May I ask why you want to do this? Is it because you want to maintain a historical record of the data? Wouldn’t it be easy to clone the sheet/table once offline so you keep your historical record ?

[cainsworth12]

I want the app require data to be put into the app to produce a quote. Currently whenever a quote is produced it changes the original information in my sheet to that amount, which will be incorrect for the next customer based on shipping cost.

I want to input data, have that data generate a quote, email that quote to the customer, while saving this data on a separate sheet to so I can reference it later.

Does that make sense?

[pravse]

Got it. So logically, you have maybe a Product Price List, and then you have the actual Orders. Each Order takes information from the standard price list for the specific product, adds on custom shipping for the specific customer, and saves it. Correct, more or less?

If so, you need two tables — one for the Product price list with a product column as its key (and make that table read-only) and one for the Orders. The Orders table will have a Product column that is a Ref to the Product Price List table. You will be adding rows to the Orders table.

When you select the product for the order row entry, you can then automatically fill in the price and other information from the Product Price List table using a de-reference expression (eg: [Product].[Cost]) or ([Product].[Weight]), etc. Add in the custom information explicitly and save.