Bit of a messy title, I couldn’t think of a good way to phrase this question…
I have a table that stores inventory items, each item can have one of three status’ “Available”, “Gifted” or “Sold”. I’m trying to create a view that only returns the name of an item where there are none with a status of Available. So for example the view shouldn’t return this item:
But it should return this one
Is this possible to achieve and if so, how?
Hey
This is def possible to achieve, however you will need to make a small tweak to your database architecture.
1. Make sure your Name field is not the primary field, and click on Edit field and change the field type to a Linked Record field. This should create a new table for you which you can call Items.
2. On your Items table create a Rollup field, to get from the table which you shared screenshot from, the Status field and have an Arrayunique(values) formula on the rollup. Call this new rollup field Status -more on how to set Rollup fields here.
3. Set a filter on the view on the Items table to show only those where Status (your rollup) does not contain Available.
If you need additional help setting this up, please feel free to grab a slot using this link! I’d be happy to go through this together in a few minutes :D
Mike, Consultant @ Automatic Nation
Hey
This is def possible to achieve, however you will need to make a small tweak to your database architecture.
1. Make sure your Name field is not the primary field, and click on Edit field and change the field type to a Linked Record field. This should create a new table for you which you can call Items.
2. On your Items table create a Rollup field, to get from the table which you shared screenshot from, the Status field and have an Arrayunique(values) formula on the rollup. Call this new rollup field Status -more on how to set Rollup fields here.
3. Set a filter on the view on the Items table to show only those where Status (your rollup) does not contain Available.
If you need additional help setting this up, please feel free to grab a slot using this link! I’d be happy to go through this together in a few minutes :D
Mike, Consultant @ Automatic Nation
Hi Mike
Thanks for the response, much appreciated. Unfortunately Name is the Primary field for that table, is there a way around this without copying each item currently in name column into a new one?
Yes!
You can click and drag the primary field to the right which will allow you to change which field becomes the primary field.
Alternatively, you could also just create a new table called items, then create a new linked record field on your main table, and link it to Items, then copy an paste all values from your Name (primary field) to the linked record field -which will create all corresponding tables on the Items table.
Eventually you will probably want to have a formula as the Primary Field to concatenate values (data from fields) to easily identify the record. -you do not need to worry about this right now.
Please let me know if this was not clear enough!
Mike, Consultant @ Automatic Nation
Perfect, thank you so much for the speedy help!
Sure, hope this helps!!! And otherwise feel free to grab a slot and we can go through it on a quick call.
Mike, Consultant @ Automatic Nation
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.