Hi, community, I’m looking for advice on a better way to organize my process for clients. (new to the process) Right now I handle this in two different bases but I’m hoping I can have all info in one. I have a client base then an inventory base. I work in the interior design/real estate is the industry. I have a client base that has the job description and address of the job that I do for a client, then I have another base that has inventory for the client and the associated address of the job.
Is there a way to have one base that shows the following path: client “A” info then I can drill down to client “A” particular job via address, then drill down to that addresses inventory sheet? I hope this makes sense. It seems silly to go to one base and put in all the details for the client except the inventory (which is done in another base) I’d love to handle it all in one if it’s possible then I can see all details for each client. Client “A” hired me to do “x” job then “x” job inventory details. I looked in the article that showed the process of the many relationships of a base but I got confused as to how to set it up. Thanks for the assistance.
Hi @Christa_Patton, and welcome!
First and foremost, get it all into one base. You don’t want to split your data between two bases like this because you lose out on the benefits of connecting data.
Once everything is combined into a single base, you’ll have a “Clients” table, a “Projects” table, and an “Inventory” table (at the very least), and you’ll connect the pieces through Linked Records, Lookups, and Rollups.
Hi I’m getting the hang of linking tables, I can link my client to a project but I’m having a hard time understanding linking my inventory to a property. The original way I have it is inventory is a base. I had a blank template with items (chair, sofa, art, etc ) and each field was an attribute of the item (price, quantity, location in warehouse etc) My method was to take the template, duplicate it rename it with the new address. I can see that over time this will be messy. How do I have an empty inventory sheet that I can “shop” from for each property. I need to link the client to property (which I have done) but then need to have the ability to create an ideal inventory from a master blank inventory. I hope that makes sense. I need to create a new inventory for each property because each one is unique. Thanks.
Hi Jeremy, Is there a way to have a master inventory sheet in a base yet have each inventory be unique to the client, and then the property? I’ve managed to link the clients to the property address (one client will have many properties that I work on) and now I need clarity on how to link the inventory to each property. I currently have a master inventory sheet that I use to shop from this creates a unique inventory for each property. Thanks!
It sounds like what you need is a “join table”; another table that sits between your “Property” table and your “Inventory” table. I’m not sure what an appropriate name might be… perhaps “Property Inventory”. This table will contain “line items” that represent a unique inventory for a client/property.
This new table will contain one record(row) for each instance of a piece of “inventory” being used at a “property”. Each record(row) will have a linked field linking it to the Property it will belong to, and then to an item from Inventory. You probably don’t want to link multiple items from inventory to a single line item (record) in this table. Each record will be linked to ONE property and ONE item from inventory. In this way, you can reuse the same items from your Master Inventory list, but still have individual instances of that item’s use across multiple properties.
In addition to those links, this table could contain fields for any data that are relevant to the use of an inventory item in a property, but may not be relevant to an inventory item on its own (such as, perhaps, “Date Ordered”, “Date Arrived”, “Price Obtained For”, “Room Placement” – I don’t know your application, just shooting from the hip here…).
This will enable you to “Group” or “Filter” this new join table by “Property” – and once grouped this way, each grouping or filtered view will represent the unique inventory for that property, as assembled from the Master Inventory list.
Hope I wrote that all in a way that is digestible and applicable for you :slightly_smiling_face:
This is quite a bit to digest and I really appreciate the details. Since my original inventory is in another base is there a way to link to that base? From what I’ve researched there isn’t but perhaps there is and I didn’t dig deep enough.
Also, I’m confused about this statement:
" You probably don’t want to link multiple items from inventory to a single line item (record) in this table.
Do you mean I want to link just the item (sofa, chair etc) to a property but not another item within inventory? It’s tricky for me to articulate this and I appreciate you walking me through it.
@Christa_Patton it looks like @Jeremy_Oglesby hasn’t seen your replies. I just tagged him to send a specific alert to him. To do that, type the @ symbol when composing a reply and the forum will show you a list of everyone in the current conversation. Select the one you want, and a tag will be inserted into your message (similar to the tags I added above for both of you). If you are composing your reply via email, you can just write out the full username after the @ symbol and it should work, but it must be accurately spelled or it won’t properly tag the user.