Automatically Add Rows & Update Columns

I’m looking for feedback on how best to implement the following addition to my app:

  • The app manages our order fulfillment process for a steel manufacturing operation.
  • I have a parent table consisting of work orders (contains primarily an order number and shipping information).
  • The child table consists of the work orders’ individual line items.

Presently we use a CSV import in the app to get the data into both tables. This works but it’s manual in Appsheet and is cumbersome to make in our accounting system. We just configured our accounting system to create an automatic, daily excel export containing all of our order information. For reference, the accounting system’s export is based around line items.

What I’d like to do is setup our app to automatically do the following:

  • Create new rows in both app tables as the accounting export table grows
  • Update existing rows as changes appear in the accounting export table

In a nutshell, I’m looking for advice on how best to configure the actions (and probably a bot to coincide the actions with the timed updates to the accounting system’s table data) to:

  • Look at the work orders and line items tables and only add new rows to both
  • Look for changes in existing rows and update those column values.

I’ve read numerous other posts here where actions are configured to do similar things on a row-by-row basis when users make changes (and other similar variations) but I haven’t come across anyone else doing something similar in bulk like what I’d like to do.

Any input or thoughts would be appreciated.

0 3 1,739
3 REPLIES 3

If I understand correctly, you have Order PLACEMENT performed in an existing system and you wish to funnel that information into an AppSheet app to handle the Order Fulfillment.

What datasource are you using for the existing Order Placement system? Could you tap directly into that datasource from the AppSheet App?

Setting aside the suggestion above, there is something fairly recently introduced known as External Eventing. You can trigger automation in your AppSheet app based on changes made to a Google sheet. I don’t know much about it myself yet. Maybe it can help?

You can find out more about that feature in the two following articles

As I re-read the information, it appears that this eventing might only happen when a person enters data into the sheet through the keyboard.

The only other option is to use third-party services such as Google Scripts or Integromat to migrate the CSV data into your tables.

John,

Thank you for your suggestions. I didn’t want to leave you hanging or think they weren’t appreciated. After looking through your suggestions and continuing to investigate how to accomplish this, I really didn’t want to use third party services. For anyone else that comes along with similar needs, here is the solution I implemented:

  • I have an Excel export from our accounting system (Sage50 US Edition) that happens daily (but only if changes occur that would affect our production system in Appsheet). This table is on our Goggle Drive account and is a read-only table in Appsheet (we’ll call it ‘Orders Table’ for simplicity). The column names are always the same and as data changes, it gets “refreshed” (added to if new data/overwritten if edited data) in the table for our app.
  • The app’s primary table reads the applicable data from the ‘Orders Table’ in two ways:
  1. The key field (CONCAT of order number and line item number) is setup with data validity so as new rows occur in the ‘Orders Table’, only those new values are shown as options for new order records in the app (duplicate entry elimination).
  2. All other necessary fields (order quantity, shipping date, customer name, etc.) are automatically filled from the ‘Orders Table’ via LOOKUP, based on the key value selected.

Since these are all standard columns, the LOOKUP formulas only work when a row’s record is edited. To address cases where something may change in the ‘Orders Table’ for a record that’s already in the app (customer changes an order quantity, shipping date, etc.), I implemented the quick update system that’s been documented here. It works great; everyone should use this lol.

Quick Update System - How to update records in your app with a push of a button

Finally, I added a bot that automatically runs the quick update system daily, off-hours, so the data is always refreshed and in sync with our accounting system for app users.

The only manual step is creating new production orders as they appear from our accounting system. However, this isn’t an inconvenience as there are other things that need to be done at that time as well, such as selecting work stations for the job, review project drawings & requirements, etc.

Hopefully this helps someone else out with a similar project in the future!

Top Labels in this Space