Create line item in field, reference it in child table

[dahchrist]

Hello -

I have a form that we are using for invoicing and invoice creation. MOST of the "Customers" of this app are preloaded earlier on when they sign an agreement. Some customers, though, purchase an item without going through the agreement process (it's a weird procedure, I know).

Basically, I have an Invoice table that has a one-many relationship with a Transaction table. The Invoice table also references a Customer table, a Bill To table, and a POC table. Here's the trouble... Not all Customers are responsible for their bill - sometimes their insurance company is; in these instances the Customer is Jane Doe, the Bill To is InsuranceABC, and the POC is John.Frank@Insurance ABC.

The current workflow is that anyone who is created as a Customer will have a billing profile created for them via bots (bot will take information from Customer table and input it into the Bill To table). The issue lies in customers who don't come through the traditional route. I see two potential options that might work, but I don't know how to implement...

The first option:

A "Joined" table of Customers and Responsible Parties. Somehow generating a referenceable list of all Customer Profiles and Bill To options to allow a user to choose from a conglomerated listing.

The second option:

User opens invoicing form, taps Customer, New AppSheet opens New Customer form (1:1) Completes the required information for Customer, Okay AppSheet saves this information as a line-item entry to the Customers table User taps Bill To, selects the newly created Bill To (which was automatically created by the new Customer form) The newly created Customer from above is available to choose (as the only entry) Bill To is a referenced table, but the Key for values is only different by a prefix (BILLTO-CUSTOMERUNIQUEID) User taps POC, selects the newly created POC (which was automatically created by the new Customer form) User taps Transactions, New AppSheet opens the New Transaction form (1:many) Completes and Submits Submits Invoice and automations run

[WillowMobileSys]

In general, the advice is to follow the existing work flow as much as possible.  Your second option seems to follow this advice.  This keeps things consistent - which is a good thing!

It seems to me that the only difference in your work flow is WHO is adding the Customer and Bill To information.  An employee up front based on a contract OR a new Customer.   So the challenge becomes, how do you identify that the user is a new Customer?  Once you have a way to do that, you'll have what you need to make the other changes.

Whether you get to the new Customer form from an Invoice or from some other view, the Customer entry process should be the same.  When it's a new Customer, it may mean that you need to lock down certain fields so the Customer cannot see other Customer data.  You may also need to add additional validation expressions to prevent the entry of bad data. 

I hope this helps!

[dahchrist]

Yep - that's what I was thinking, too. I just can't figure out how to either isolate and identify the new customers (outside of using something like General Customer as the Customer). The API was working to create the new customer's as BILLTOs, but it was not capable of updating the fields in the form because (I assume) the updated BILLTO existed on the backend data tables instead of the local table that existed when the sync was initiated. Is there any option to force AppSheet to reference to a backend dataset vs what was last synced to device?