How to show and calculate the quantity of each SKUs with different expiry date?

[Neil_Shih]

Hi guys, I’m new here.

Now I am developing an inventory management app in order to do “Expired First Expired Out”, which means the item which expires first, we will pick it up first.

I have 3 Data Lists, “Meal List”, “Meal In” and “Meal out”, in the app. For Meal List, it contains SKU, Description, Net Stock and a virtual column called Current Stock. For Meal In and Meal Out, both of them provides a form for workers entering the data including SKU, Qty and Expiry Date for recording the meal in and out of the chiller respectively.

The problem is I want to show the Current Stock ( the total quantity of meal in - the total quantity of meal out) by each expiry date of each SKU in ascending order. But unfortunately, I can only show the Current Stock without relevant expiry date of each SKU.

What the interface shown now:

SKU: ABC
Current Stock: 100

Quantity Expiry Date
130 10/20/2019
45 10/31/2019
30 11/02/2019
5 11/11/2019

Current Stock = Quantity of meal in - Quantity of meal out
= 210 - 110
= 100

The quantity of Current Stock (100) is correct, but the app only shows the related Meal In record (Quantity and relevant expiry date), and it does not deduct the quantity of meal out (110 qty with 10/20/2019 expired) which I entered the data before.

What the interface shown I want/imagine:

SKU: ABC
Current Stock: 100

Quantity Expiry Date
20 10/20/2019
45 10/31/2019
30 11/02/2019
5 11/11/2019

Anyone can help me? I get stuck into this problem more than a few weeks.

Thanks in advance.

[WillowMobileSys]

Neil HI and welcome to the AppSheet community!

I get main gist of how the app works and the problem. We need a few more details to understand what might be going on.

How are you recording the Meal In and Meal Out entries? Same table or different tables.
How do you calculate the expiry date line items?

[Neil_Shih]

Hi John,

Thanks for your help.

How are you recording the Meal In and Meal Out entries?

–> For meal in, the expiry date set as “Today () + 2”

–> For meal out, the expiry date is typed by workers according to which bath of stock they will pick it up.

–>I am not sure what tables mean, but they are in same excel file with different sheets.
Or may be the captured screen shown as follows can answer you.

How do you calculate the expiry date line items?
–> Set a virtual column: [Expiry Date] - today() for both meal in and meal out table

–> But for meal list, I do not have any column about expiry date, should I add one virtual column about it?

–> Moreover, I want to have a function to alert the workers which batch of SKU is expired and remind them to throw them away.

–> In fact, I hope to use many captured screens to explain what the problems I face. But unfortunately, it only allows me to upload one photo only.

Thanks in advance.

Please let me know if you need more details.

Thanks again.

[Steve]

Neil.Shih:

I am not sure what tables mean

help.appsheet.com

Tables: The Essentials

Key insights to add tables in your app.

[WillowMobileSys]

Ok, so we have little better picture.

For the calculation part. we are most interested in how you calculate the numbers on each line. You had posted that you have a line item showing as 130 10/20/2019 but should be showing as 20 10/20/2019.

How are you calculating the 130? Can you show us the formula you are using?

By the way, yo should be able to apply multiple pics, you just have to do it one at a time.