Inventory app - virtual column formula to show available stock based on # of serials of a model

[nadim_dayoub]

I am working on a simple inventory app and trying to create a virtual column that would represent the available stock for a model based on how many unique serials I have.

• Data Sheet of all my models: screenshot-docs-google-com-2021-05-12-08-20-03 — ImgBB
• Data Sheet of all my Serials: screenshot-docs-google-com-2021-05-12-08-20-32 — ImgBB

and for my second question, would it be possible to remove available stock based on if a serial received a timestamp, PO or my amount?

I gave each Serial an amount of 1 hoping to do something with the stock availability, so when I edited the serial to add a PO # I could just take the Amount of 1 away to adjust the stock #… but my theory failed… and here I am

[nadim_dayoub]

• Screenshot of my Model View: screenshot-www-appsheet-com-2021-05-12-08-31-24 — ImgBB

I only could post 2 links at a time, thought maybe this view would also help with the question.

[nadim_dayoub]

I thought maybe: COUNT(SELECT(Inventory[Model], TRUE, TRUE)) would work, but I am still getting 0.

[Marc_Dillon · Accepted solution]

From your 3rd screenshot it looks like you’ve maybe renamed the “Related Inventorys” REF_ROWS virtual column to just “Inventory”. If that is the case:

COUNT( SELECT( [Inventory][Model*] , ISBLANK( [some column**] ) ) )

* “[Model]” here can actually be any column in the child table, doesn’t matter.
** Replace “[some column]” with whatever column you want to use to exclude a serial from the count when it is filled out.

[nadim_dayoub]

Thank you for replying! Yes, I did rename “Related Inventory" to “Inventory”.

I used this: COUNT( SELECT( [Inventory][Model] , ISBLANK( [Amount] ) ) )

and I am getting this error:

ATM for my test, I do not have any entries under the Amount column set to 0

[Marc_Dillon]

Hmm, that’s weird. Number type columns don’t have a “Precision” setting. I would try to fiddle with this setting to see if that fixes that error I guess.

[nadim_dayoub]

Alright, error went away when I set that option to 1.

But some reason “Available” is still showing 0.

Once again, thank you for taking time out of your day to help me out on this.

[Marc_Dillon]

nadim_dayoub:

But some reason “Available” is still showing 0.

I’m assuming you think this is incorrect?

nadim_dayoub:

ATM for my test, I do not have any entries under the Amount column set to 0

Not sure what this means, but a 0 value is not the same as being blank.

I’m not sure why you chose to use this [Amount] column here. Based on your initial post I would have assumed you would go with [Timestamp] or [PO].

[Marc_Dillon]

Ohhh. Maybe you’re wanting this?

COUNT( [Inventory][Amount] )

[nadim_dayoub]

I only was doing it that way because I didn’t think you could “take away” “available” amount based if a column was blank or not.

Buttt, sorry to bother you 1 more time. But when I ran a test by adding a PO to a serial, available show up as 0 vs 9 from the total amount of 10

[Marc_Dillon]

It WAS showing 10, then you added a PO for a single record, and it went to 0?

[nadim_dayoub]

Correct

[Marc_Dillon]

Maybe a key column issue? What is the key set as for the Inventory table?

[nadim_dayoub]

Ohhhh! I def misunderstood. I changed it to PO and everything works!!!

Thank you so much for your time!

[nadim_dayoub]

[Marc_Dillon]

Yep. It can’t be set as [Model], it has to be unique. You could try to set the [Serial] as the key, that should work in this case, but I’d also suggest always using a UNIQUEID() column on every table.

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

[nadim_dayoub]

Marc_Dillon:

UNIQUEID()

As soon as I replied, and I switched the key and it worked. I though I had to use the same key for both sheets, for the tables to group.

But now im running into the issue of instead of 10 going to 9, it went to 8.

[Marc_Dillon]

nadim_dayoub:

I though I had to use the same key for both sheets, for the tables to group.

Nope. That’s what the “Ref” type is for.

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

nadim_dayoub:

But now im running into the issue of instead of 10 going to 9, it went to 8.

[Marc_Dillon]

Do you have 2 records with the same serial number?

[nadim_dayoub]

That is what I am reviewing now. Because in other models it works perfectly.