is there a way to create a table with a list of products from another table that have the same vendor, in other words have a self updating table with only Coca Cola products without having to copy all the info from the main table its pulling from?
Yes that is possible. Without seeing your table setup I have little info to work with though. Just to understand your question better, are you looking to make a table for each Vendor separately, or a table that has all the Vendors only mentioned once in each record with all their products mentioned in the same record?
If you are looking for a table for each vendor separately, you will need to do a New Table for the said Vendor (manually for each vendor). In the second Field make it a Linked Field to which ever table you want to pull the data from, select the Vendor, then set the remaining fields as Lookup fields to display whatever info you want there.
If you are looking to make 1 table for all vendors where the Vendors are the Primary Field, then choose the Field in the current table where the Vendors are mentioned, Customize Field, Link to record, Create new table. This will create a new table with all the vendors, then you can do the same as above "set the remaining fields as Lookup fields to display whatever info you want there. "
Was that helpful?
I’d like a “master product list” table that holds the item name, vendor name and previous order dates. then id like to populate additional sheets based on vendor that will allow me to look at one specific vendor when placing an order.
in other words the first sheet would have:
Coke Cans - Coca Cola -1/1/2020
Pepsi Cans - Pepsi 1/15/2020
then the additional sheets would automatically pull all products that have Coca Cola listed as vendor to the Coke order form and Pepsi filtered to another.
Ideally when I add or remove a product from the master sheet I’d like the change to be reflected in the order sheet.
Does this make sense?
and I’ve stumbled onto filters which might work for filtering by vendor. now I just need to figure out how to perform the calculation as to how much order history to reference for each product, also if I keep creating a new column for every order it will probably get pretty lengthy. any tips?
I see you had opened many topics with many questions. In order to be able to help you in all of them, it would be best if you do the following:
- Share a screenshot of your current table(s) (or better yet share a view only link)
- Make a diagram of what you are trying to achieve.
For example, regarding the Filters option, it would be better if you use the Group option. This will group the items by whatever category you choose (for example Vendor) and it will show you the sum of the items.
This is a screen shot from the spreadsheet I am using currently to perform this operation. Hopefully this helps illustrate what I am trying to do. What I need is more flexibility when calculating historical averages depending on the type of product I am dealing with. Additionally Id like to archive the previous orders after the last 4 most recent so that the sheet doesn’t get out of control. Thanks so much for helping me with this.
Please delete this link as this will allow anyone to go on your sheet and change whatever they want.
Ok now for your sheet, I think you need to do the following:
You need to have 1 sheet for orders. This will contain all the orders you do, it should include at least the item and the date of the order.
If you are on the Pro Plan, you will be able to have a Graph like the attached screenshot, this can be utilized to group items by whatever category you want.
- If you do not want to invest at the moment in the Pro Plan, you will need to have 2 Fields. One would be Duration to Date and the other would be something like History Category. I have made that in this sheet. https://airtable.com/invite/l?inviteId=invhjeEtET7NSf2qQ&inviteToken=41e795e8dce823f8423e0ae40d35208888cc6820487716a407ca7116adb7f255 (this is a view only link, feel free to copy this base into your own workspace so you can play around with it).
The Formula i put in the History Category field, is a very simple formula that you can change to your own needs.
- Now that you have this field, you can Group (not filter) by 2 Fields, one would be this History Category and the Other would be Vendor. (already did it in the above base for you to check it).
Is that close to what you were thinking of?
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.