Linked Forms for Quote Tool

[KimA]

I am trying to create my first app and have run into some roadblocks so I am hoping someone can help. My app flow is this:
#1 Select existing customer or add new customer (unique Customer ID)
#2 From the Customer Details, use action to open Step #1 - Quote Details Form (unique Quote Detail ID) that allows the user to use the form multiple times to add materials (item and quantity)- must reference Customer ID automatically (I don’t want my users to have to select the customer from a dropdown list each time)
#3 Once materials have been added, use action to open Step #2 - Customer Quote Form (unique Quote ID) that calculates the sum of all of the materials that were just added and perform further calculations to determine the final price of the quote.

Issue #1 I have set up the actions so the forms will open up but I can’t seem to have the customer ID carry over so they will always link back to the customer.
Issue #2 I need to be able to add multiple materials using a Quote Detail ID that will stay the same for the current quote but change if we need to create a new quote for the customer

I know I am asking for a lot but your help would be greatly appreciated as I am under a tight deadline.

[MultiTech]

Check out linktoform(), you can pass along information to the form that is opening (though this only works when CREATING records, not modifying them).

I think this will work in both of your situations, you just need to pass along the ref.

help.appsheet.com

LINKTOFORM()

Constructed deep link to form

[KimA]

Unfortunately I find the AppSheet documentation not detailed enough to help me figure this out, which is why after 3 days I am nowhere with the development of this app. Perhaps this program really is only for experienced IT people and not the general public.

[Lynn]

Hi @KimA Did you check out any of the sample apps?

[KimA]

Yes Lynn, I have looked at a lot of the sample apps. Before I started anything, I took a deep dive into every single bit of the Orders and Quote & Proposal sample apps. I used that information to try and build my app. And when that failed I tried to use the Orders sample app and just replace it with my own data. But as soon as I started to rename the columns, it all broke.
Over the last few days I have looked at many of the other sample apps, read the documentation and read a lot of the questions posted by the community to try and figure this out.

[MultiTech]

KimA:

But as soon as I started to rename the columns, it all broke.

If you were starting from a sample app and started changing column names, that would cause things to break.
There would be formulas elsewhere in the app that use that same column name; when changing something like that, it’s best to do them one at a time - this way you can catch all the errors around the editor and update them so they use the new column name.

[KimA]

I tried doing it slowly and systematically. I am wondering though, should I keep regenerating the structure or just save as I go? I noticed that sometimes when I regenerate the structure, it causes some columns to disappear or change type or the formula but I don’t understand why.

[MultiTech]

Unless you’re changing the structure of columns (like adding, moving, or deleting columns) you don’t need to regenerate the column structure - merely rename the columns in both AppSheet and your data source.

But just changing the names doesn’t require a regen.

[KimA]

Thanks - I am going to try to upload my data to the sample app again and see if I can get it to work this time.

[Steve]

If you want to rename a column, do these together:

• Rename the column in the spreadsheet .

• Rename the column in the Data > Columns tab of the app editor.

Optionally save or regenerate at this point. Below are additional changes you may need to make as well. If you save before doing them, your app may break.

• Update references to the column in any expressions in your app. In most cases, references to the column’s old name will produce errors that will prevent the app from working. See the Info > Errors tab in the app editor for help finding the problem references.

• Update references to the column in any views (UX > Views). Note that the column may have been removed altogether from views and need to be re-added.

• Update references in any slices (Data > Slices). Note that the column may have been removed altogether from slices and need to be re-added.

• Update references in any report or email templates.

[MultiTech]

Let’s tackle one at a time:

KimA:

Issue #1 I have set up the actions so the forms will open up but I can’t seem to have the customer ID carry over so they will always link back to the customer.

For this, if I’m understanding your data structure, you’ve got:

Customers
Customer Orders
Order Details

If you have a ref connection between order details and customer orders, you can use a de-reference to pull the customer ID from the order (and populate it into the order details). In the app formula space, put a de-reference like this:

[Customer Order].[CustomerID]

Where [Customer Order] is the reference column to the orders table (from order details), and [CusomterID] is the column (in the orders table) that holds the customer selected.

Here’s a link to de-ref docs (I know they can be sparse, but that’s what we’re here for!)

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

I think this might solve your other problem as well, as it seems similar in nature.

[KimA]

Thank you for trying to help me. Unfortunately the related quotes are still not showing in my Customer form. I used the dereference expressions in my Quote and Quote Details tables as well as a double dereference expression in the Quote Details table as per the instructions. Not sure what I am still doing wrong.
Customers Table - unique identifier is Customer ID and I have a concatenated Customer Name column. I have a Related Quotes virtual column with: REF_ROWS(“Quotes”, “Quote Id”) and a Related Quote Details virtual column
Quotes Table - unique identifier is Quote ID. I have a Customer Reference column (ref Customers table) with a formula: [Customer Reference].[Customer ID] There is also a Related Quote Details virtual column (List) with the formula: REF_ROWS(“Quote Details”, “Quote Id”)
Quote Details - unique identifier is QuoteDetail ID. I have a Quote ID ref column which has a formula: [Quote ID].[Quote ID] I also have a Customer Ref virtual column which references the Customers Table and has a formula: [Quote ID].[Customer Reference]

With all of this the form does have a related quotes section but does not populate any of the information. And when I go to the Quotes form, most of the columns are not showing up.