I am trying to do the following flow for an inventory app to audit rental apparments and check if anything’s missing between tennants but can’t wrap my mind around it:
Have an ITEMS table where I will have all the individual ITEMS that usually are in an apartment
Have an APARTMENTS table where I will have all the appartments I manage
3A) Have a table for each appartment with the items this apartment have, example: APT 1 and bring specifi ITEMS from the ITEMS table, this way if I edit the ITEM name on the ITEMS tables, all the APT 1, 2, 3 that use that specific ITEM will be udptated
3B) At this point, having related the APT 1 with some ITEMS, I will like to add columns for example QUANTITY of this items in that specific appartment so as to validate if I have to RESTOCK
I was able to do all of this for 1 apartment, but I can’t seem to understand how to do this having multiple apartments bringing up specific items from the items list and add more properties to that
That way on my front end I will have a HOME with all the aparments, I click APT 3 and I would get a list of the ITEMS available in the APT 3 so I can go around the apartment validating everything’s in order, and if some item is missing I can mark it as MISSING but this won’t affect any other apartment
Any ideas on how to accomplish something like this?
Probably the easiest way to do this is to use a 3rd table to connect Apartments to Items.
This 3rd table would have the following columns:
ApartmentID (RowID from Apartments table)
ItemID (RowID from Items table)
Quantity
And you would have one row in the table, per apartment, per item.
The only downside to the approach is that it will use quite a few rows.
A more efficient approach would be to store the inventory for each Apartment in a single column as a JSON object. But that’s a lot more complicated to setup and manage.
What you are describing as a 3rd table is exactly what I need, but I don’t seem to get how to accomplish that, when I use QUERY or LOOKUP and those kind I only get to the function to bring everything to 1 row so from there it’s impossible for me to keep on adding columns such as quantity, stock, etc.
I don’t mind the inefficiency of this because it’s an internal tool not for profit
I would love to know how to do the JSON object but I think I am not skilled to that, but I am sure with some assistance I could manage to get working the first idea you proposed.
although I failed to clarify that I was looking to do this on the tables DB side because imagine there are like 1000 items in each appartment so going to add 1 by 1 is a pain
Go from the front to the back I can understand how to do it, but I am busting my head to try and get to populate the table just as you did but in an automatic way
One thing I am thinking as I see your video is maybe add a previous screen to the one you did where I import the 100% of the items, and then via CHECK BOX quickly select all the ones that should be in that appartment, submit that, and go from there editing quantities and stuff
do you think that would be difficult to accomplish?
your video helped me a lot to get started I’ll start playing around with it thanks
Yes! I saw that on the paid version there’s the API world opened, that seems what I was looking for but as I am testing Glide I didn’t have that option
I think for now the best approach is to do what you showed in the video, and do a bulk manual upload of a spreadsheet, neat idea
I’ll try it out and come back here so you can take a look
Other thing I have in my mind that maybe you have examples already done as they are standard things I want to add in the future:
Reset all QUANTITY values to ZERO o to a DEFAULT table… but I think this only could be done with API too isn’t it?
Make the inventory exclusive for the logged user… meaning that if I log in with other account, I see the FACTORY settings of the inventory and not the ones stored by the person who previously did the inventory
I am sure there are plenty of examples with this that you can point me too
I did what you told me, the manual version… it’s cool but you are right its limited…
because, supposed I now have 45 appartments, and I add 3 items to the MASTER ITEMS table… later, I should go to each of the 45 APT-ITEMS tables and add those ID so it’s practically useless
I should learn to do the automatic way but I don’t even know where to start LOL
right now, each aircraft has it’s own TABLE that I manually brought the IDs from the master ITEMS table
so, for manual development it works fine… but the thing is I don’t have idea how to make this into an actual app that works dynamically without the need to create manually a table for each
also, as I told you up here, if I want to add an ITEM, now I would have to go to the MASTER ITEMS table, and then Manually add it to all the Aircrafts table that use this item
that’s not scalable for when I have 50 aircrafts and 5000 items LOL
Haven’t followed this thread very closely, but using your surjet app as an example, wouldn’t it make more sense to merge all of the aircraft tables into one table with a column that indicates the Aircraft ID, and then create a relation from the master aircraft table to the aircraft items table, and use it to determine which rows should be visible when navigating to the detail screen of an aircraft in the master aircraft table? Doesn’t solve the issue of adding an item once and having it show for each aircraft, but it would be a first step to simplify. Then whenever you add a new item, you could have a single row helper table to take in the inputs for the new item, then build the json payload with a mutation repeating for each aircraft and have it write multiple rows to the shared aircraft items table.
So in the end I envision two tables. One that lists the aircraft and another that lists each item for each aircraft.
yes! I missed the RELATION TO SCREEN that @Darren_Murphy showed in his video that filters the products from a certain aircraft so that would solve the duplicate tables and just have 1 for the full inventory… I missed that part and that snowballed
I’ll try to merge all items tables into the inventory one now and add a FULL INVENTORY screen on the front end
so that way the user can first go to the FULL INVENTORY, add an ITEM or EDIT one, and then the user can go to an aircraft and add it from the dropdown selector
I attach to you a screenshot of the tables structre is now very neat using your advice I only have 1 table for the full inventory and on front end I filter all that so it’s working nice
I als attach something else to see if you can spot what’s going wrong… As you can see I have a CATEGORY table so I can group all the inventory products and filter them as well
I am now trying to work on the front end to be able to ADD and DELETE categories… the DELETE part I am trying that the DELETE button only shows up if there are no products associated to that category, so user should first go and delete all products in a acategory to be able to delete the category itself and avoid breaking all the app
So I am conditioning that component to show up only if the field where it’s related to products is EMPTY, but as you can see on the screenshot the button shows up anyway…
I’d recommend creating a separate action for the Delete, rather than doing it inside the Edit Form.
I’m not exactly sure if that’s why your visibility condition isn’t working, but it’s generally a better practice to keep these actions separate.
I tend to check if the relation is empty instead of checking the lookup. Not sure if that would make a difference or not.
The problem with Add Forms and Edit Forms is that they are in a somewhat disconnected state from the table, so computed columns in the table can be inaccessible as they are not computed until the form is submitted and the values are written to the table. That might be what you are running into, but I’m not sure off the top of my head.
yes, I tried it with RELATIONS first but didnt work, that’s why I added the lookup to see if that would solve it
I think it’s what you are saying and it’s disconnected, so that puts the user in a dangerous position, because if he deletes the category, he would affect all the aircrafts and make a mess
So there’s now workaround with this? I think I will take that button down and the client will need to request for me to edit the table manually
So in EDIT mode I am stuck… it’s strange because if I pull up the DATA tab, you can clearly see that it can access the cell, but don’t know why it’s not able to read it and it bypasses it
So, instead of adding the VISIBILITY condition to that button, I left that button alone and created a confirmation screen
To the category table I added an IF ELSE THEN column that checks if there are related products and then saves EMPTY or FULL to that row…
Now, on the confirmation screen I condition that if EMPTY it can show the YES-NO confirmation screen… but if FULL it displays a message explaining the user that it won’t be able to delete unless he goes and delete the related items first
I’d still recommend creating a separate Delete action.
Are you aware that you can delete the Category and all related Products in a single step?
What you would do is use a custom action with the following steps:
Delete Row → Relation to Products (this will delete all related products)
Wait for condition → Relation to Products is empty (wait until all related products are gone)
Delete Row → This item
If you decide to do the above, you’ll also need a conditional branch in your action. It should first check if the relation is empty. If it isn’t, do the above steps, otherwise just delete the current row.
But again, I would really recommend doing this outside the Edit Form. What you are doing now is just asking for trouble.
