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

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.

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

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

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

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.

1 Like

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

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.

1 Like

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.

I’m assuming you think this is incorrect?

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].

1 Like

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

Thank you so much for your time!

1 Like

Ohhh. Maybe you’re wanting this?

COUNT( [Inventory][Amount] )

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

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

Correct

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

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.

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.

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

:man_shrugging:

Do you have 2 records with the same serial number?

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