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
Solved! Go to 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.
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.
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.
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].
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?
Ohhhh! I def misunderstood. I changed it to PO and everything works!!!
Thank you so much for your time!
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.
Do you have 2 records with the same serial number?
That is what I am reviewing now. Because in other models it works perfectly.
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |