A look into how the relevant data tables look: There is one table for Shops, and one table for Customers. Each customer has a different pricing deal at each specific shop for discounts. So there is a 3rd table, customer deals. In this table, the customer ID and shop ID are written in columns with the specific pricing for the customer at that shop. These rows are added automatically when a customer signs up via Airtables. There is a template column that combines the two ID’s to make na unique one for that specific customer at that specific shop. When a transaction is happening in User’s table, it pulls the cashiers shop ID and the relevant customer ID and matches it in a template column just like in Customer Deals. A relation is then done, and it has been working this way for a few months.
We now need to move the customer deals to bigtables due to the row limit. While bigtables can still do the template column, we cannot match it to a relation in the Users table ince its a computed column. I do realise it’s possible to write hat {shopID}{customerID} as a normal column, either via Airtables or a button. However I’m curious if anyone can think of any other solutions for this problem that would just allow me to pull the data up to the User’s column differently.
I’m not sure if it could be applied here, but one approach that I have used is to pull one or more joined lists of non-computed values through a relation or query (which is supported), and then pass those lists into a JavaScript column for processing.
Unfortunately I have no experience with Javascript. Is there any way you could explain a bit further? So I can do a relation column to pull the shops deal by matching the ID in users to the one in bigtables, and do the same for customers. Am I on the right track and how would I proceed from there?
Yeah, that’s the general idea. Can you describe (or show me) in a bit more detail how you currently handle this with regular tables?
For example, what does your Deals table look like? Is it one row per shop per customer, or one row per shop per product per customer, or…?
How many matching rows would you typically expect to get, and what do you subsequently do with them?
oh, and just a thought. If you have the CustomerID and ShopID, and both of those exist in your Deals table, why can you just construct a Query instead of a relation with templates? Maybe I’m missing something…?
Okay so from Airtables when a customer signs up it will generally add rows for that customer for each fuel station they select.(if they select 6 fuel stations, 6 rows all with the same customer ID but different fuel station IDs) This screenshot is from customer deals:
Airtables is used to do the pricing etc, which then gets written into that same row for each fuel station as they have different pricing options. This screenshot is also from customer deals:
How it worked in normal tables was the TMP column in Customer deals matching the IDs. They’d go to the fuel station the stations ID would pull, once a vehicles reg number is entered it looks up the customers ID related to that vehicle. a TMP column is made in the same way was from Customer Deals. A REL column is made. The REL column in Users table has the following look-ups:
An ITE is set up that if they choose disel, use diesel customer and pump price. If petrol is chosen, use that. Etc.
The issue now is that REL column is going to be pulling from bigtables…
Yeah, so my question is can you not simply replace that relation with a Query, and then use Single Values through the Query instead of Lookups?
Did a bit of tests that seems to work, thank you! Will implement across in a test app and come back if theres an issues.
This seemed to work however another issue that is relevant to this popped up and I’ve tried multiple different queries etc. On the frontend, a user is able to see a map of all fuel stations where they have a deal at. The way this works in the data is a REL column between our fuel stations table and customer deals(the non-bigtables one), as so:
Then a look up column of that relation to pull the customer ID’s from original Customer Deals:
From there the data source of the map was just fuel stations with the following filters:
I do realise you cannot see them in their entirety however the bottom 2 aren’t an issue and I’ve been able to do them in a query, and make that my source:
The issue is with the top one. Linking the look-up column to the customer ID thats logged in on that page now that Customer Deals has moved to bigtables. You cannot use look-ups for BigTables, I can do a joined list however that cannot be used to filter data in the map function nor in querying the data as its a computed column related to bigtables. I cannot use queries inside of customer deals/fuel stations unless we add a button in to open up a new screen for them and I’d prefer to avoid that, unless its last resort. I am really stuck so please feel free to ask for more info if needed.
hmm, I think this is something I’d need to see in front of me, and perhaps have a fiddle with. You mentioned earlier that you have a test App. Is that something you could give me (builder) access to?
