This is probably pretty straightforward but for the life of me I just can't figure this out, and it keeps coming up. How on earth do I use the data in a column in the table to get related data from another query?
Here are my two scenarios I'm stuck with right now. I pull in a whole data table through a SQL view that my users are used to looking at that I want them to be able to edit. When we pass the data back, however instead of sending back the text they've picked in one column, I need to send back the related primary key that the text lines up with from the source table.
So, for example the Product Type column in Table1 shows "Baseball Hats" which in my Product_types table in SQL would have Product_type_Sk=5 for "Baseball Hats" but for reasons beyond what I can explain here, that "5" will not be collected in the query that populated Table 1, I need to get it after the fact by comparing the string in Product Type column to another query I've run (Select Product_type_sk, Product_type from dbo.Product_types)
for simplicity I tried adding a new hidden column to the table and using array.filter and array.find to try to match it up to the currentrowsource.product_type but just couldn't get that working.
My second scenario is similar, I have a tags column where I want the user to pick a discount rate, it would then look up the matching discount data query from dbo.discounts to pull in additional fields containing numerical values that I can use to manipulate the pricing in other columns (Level1 column, for example) automatically based on the tag selected... I know how to do the math in each column but I cannot figure out how to reference ONLY that row in the query so that I can add in the math formula to the 'mapped value' field for the Level 1 column.
Scott, that part I know how to do once I get the matching primary key, but I'm stuck on how do I get that key from another query by somehow joining them on the text.
I suspect the answer is a transformer before I even pull in the data to the table, but I can't find any examples on here where the transform brings in data from another query, only examples where they manipulate the existing data with text formatting or math functions.
I'm well versed in Sql but very much a novice in JS and learning on the fly here, so maybe the answer is obvious to others.
You can see Disc Description is a drop down tags column that comes in with values, and that value is in the Discounts Query as well (only once). Now for each row, I need to bring in the matching discount levels from the Discounts Query (level 1,2,3,4,5 etc..) so that I can do the math to calculate the discounts. And then if someone changes the drop down on the Disc Description column (field is editable) or changes the list price, the calculations will update.
So for row 3 it would read Standard 40%, List Price =compare at * (100-(variant price...))/100=89.99
and
Level 1 = Compare at * (100- level1 (Standard 40%))/100= 62.99
Level 2 = Compare at * (100- level2 (Standard 40%))/100= 53.99
etc...
obviously I know how to do the math, but can't figure out how to pull in the related row values from Discount query depending on what tag is picked in disc description.
OK this seems like something I would need to see what is happening... just curious have you set the primary key in the table component?
There is also Retool office hours as well....
@Tess thanks for your help today in office hours (@ScottR thanks for the idea, I didn't know about those).
Your code worked like a charm to get the data I needed, and now I'm trying to get the edited data into the mix, I've been able to access the edited boxes programmatically, just struggling with the if statement to get it to apply across the board... here's what I have right now in the mapped value column for a test column, trying to just get the value from 'compare_at' for the row if the cell is edited, otherwise get the original value:
{{ let result=''
let ball=!table1.changesetObject[currentSourceRow.product_sk].compare_at
if(!ball){result=currentSourceRow.compare_at;} else {result = table1.changesetObject[currentSourceRow.product_sk].compare_at; }
return result}}
setting the mapped value to just table1.changesetObject[currentSourceRow.product_sk].compare_at works fine for the one row that would match (though it also auto-sorts the column for some reason and shoves the matching result to the bottom which is weird).
for reference for anyone who is looking at this thread, we determined the best way to achieve what I wanted was to reference the other table inside of the mapped value property for each column, instead of using a transformer. the working code to calculate pricing is as follows:
Tried something else based on another post in the forum, still stuck. this works to show the correct value on the one row that is edited (and auto sorts the column annoyingly), but the rest result in nulls
One concept we didn't address in office hours is that mappers and custom columns can successfully display the data that you want to see, but it can be difficult to reference those frontend values in other columns.
For example, in this screenshot, Level 2 in the table shows "Discount 35%" because it is based on the Disc Description's changed value. However, if I read {{currentSourceRow['Level 2'] in another column on that same row, I'll get "Standard 40%" since it hasn't been saved back to the database yet.
If I wanted to get Level 2 = "Discount 35%" in another column, I'd have to either save the pending edit to the database or repeat the calculation from the Level 2 mapper in the other column
as for your second reply, that's just a point of information, right? since we were able to do this successfully? or are you suggesting once I change the tag column to something else before saving this code will fail?
The code shouldn't fail! Although, it's always worth doing some testing to ensure you're seeing the expected values
That was more of a warning that since we're just modifying the frontend value in the table column, it could be difficult to reference these modified values in other areas of the app.
If you see that it is working as expected, that's great!
got it! Won't be an issue, once they're satisfied with the numbers from the changed fields they'll then hit save and I'll pass back the updated data to the SQL database. thanks for your help!
