Sum Null Variables without Error

Jared · August 13, 2020, 5:53am

Hi all -

I am currently building a POS system for my mom's (very small) business. This system is not designed to process payments (although that would be an awesome feature), but is simply meant to keep a record of transactions in order to more easily keep track of sales for tax purposes (she currently does this all with pen and paper, and excel terrifies her).

I am trying to design a system that can handle up to 20 unique items in an order, but that doesn't allow you to add an item until the first one is added. Once Item #1 is added, fields for item #2 are made visible. Then, at the bottom, the totals are summed in order to calculate sales tax and order total.

In order to facilitate this, I have 20 pre-made blocks to act as the form that needs to be filled out. I was hoping to sum the unit price variable in each of these blocks to calculate the order total, but because the variable is null the sum function spits out an error.

Currently, I am working around this by setting the initial value of the price variable to 0, but that doesn't look as pretty, and is also annoying to use since the app user has to delete the zeros in order to enter a price. I also considered a massive nested IF(), however that is obnoxious (although what I will probably end up doing in the end if there aren't any better ideas).

Any suggestions or workarounds?

Thank you!

Alyssa · August 14, 2020, 9:55pm

Hi @Jared,

Thanks for sharing what you're building!

I have a method that could simplify how you are setting this up and also wouldn't limit the amount of entries you put in an order.

Instead of thinking about creating blocks that you unhide, you can create a screen that adds order entries to a list (as many as you'd like) and displays the totals on the bottom. To set this up in tables, you would create a table of order entries that you can keep adding rows to, and another table where the tax calculations and order totals are summarized in.

For an example, here's a screen I created:

And this is the list screen of all the orders:

This is how I it set up:

First for the tables, I created the following:

Item Table, with columns for item name and cost.
Order_Entry Table, with columns for items (rowlink to item table), quantity, and cost of entry (cost of item multiplied by quantity).
Order_Summary Table, with columns for subtotal, tax, and total calculations.
Order_ID_Counter Table, which helps iterate a unique ID for the orders. (For this concept, you can refer to this article: How to Build a Unique Row ID Automation)

For the app, I started by using the App Wizard to create a list screen and a details screen of the Order_Summary table.

On the list screen (which I renamed to "All Orders"), I updated the automation of the "+Add" button to iterate the order number, create a new order row with incremented order ID, default order totals to zero, and then navigate to the details screen of the newly created row.

On the details screen (which I renamed to "New Order"), I added a column list object of the Order_Entry table, which is filtered to only display entires for the order number on the screen.

Above the order entries, I added two fields for user input and a button. One field is a rowlink that allows the user to select an item from the Item table, and another field for the user to enter a quantity. The "Add to Order" button has an automation which takes the input of those two fields and the order number on the screen to create a new row in the Order_Entry table (which then displays in the list on the screen).

I then added submit order button and cancel order buttons. (Cancel would delete the row, Submit would take you back to the previous page).

I hope this example helps and can show another way to build your app. Let me know too if you have any questions on how to do any of these steps, and I'll be happy to show you.