I’ve got a bit of a weird thing that I am not entirely sure if it’s an issue of my database design or can be solved another way and am hoping someone here can help, which would be massively appreciated :slightly_smiling_face:
I have two bases for a webstore, one which holds high level information (items for sale, vendors, master order records) and the other base has individual records for each unique item in each master order record.
Excuse the awful drawing and writing, but the below diagram illustrates how I’ve structured things so far.
Black lines with two arrows show linked fields. One black arrow shows synced tables, and the blue line shows what I want to achieve. Red line shows that the field already exists, but doesn’t allow
The end result I am aiming for is to be able to report on sales associated with each vendor (i.e. Vendor A has sold $500 of items this month). The problem I have is what seems like it would be the logical link of pulling the vendor detail in via the item doesn’t allow me to then total up based on the Vendor anywhere.
I could add in a second link field, to directly connect line items and vendors, but then I’d need to implement a solution to literally replicate the exact data that already exists in the lookup field from the original Vendor table, and ideally I’d rather avoid tacking on extra ‘fixes’ like that wherever possible.
Has anyone come across this type of problem before and/or has a potential solution/advice?
If it helps further illustrate I’ve put together the following example bases:
The empty text field in ‘Example base 2 → Vendors’ is titled to give one example of what I am trying to achieve.
Thanks in advance! :slightly_smiling_face:
(Apologies mods for the double post, I realised my posting this in ‘Airtable Sync’ was probably the wrong place to get help!)
