I want to change TableName property in a GUI generated query to update a table via bulkupdate.
Since I can't alter the query as it was built in GUI mode I just want to change the property holding the table to be update before my editRows query runs. Is there a way to do this>
Hey @jhp333!
What kind of resource are you using? You might try duplicating your query so that you can have a separate one for each table and then conditionally trigger or enable the one you need. Depending on how many tables you're switching between and how the query is triggered that could get cumbersome though. Interested to know more about your setup!
Kabirdas,
I’m using MS SQL. My app generates a new table each time the user performs one of the app’s functions. The table name is a concatenation of a description and timestamp to make it unique so I need to be able to dynamically change the name of the table. What I really need is a way to dynamically duplicate the sql that writes the BulkUpdate. I’m just not that good with Javascript so I’m not sure how to construct a query with using the Retool tables changeset and recordupdates properties. Certainly someone out there has that code written. I just need an example.
Thanks
John
Happy to help find the right syntax! Do you have an example snippet of what the SQL code would look like if it were hardcoded so that I can see exactly what needs to be included? Alternatively, you can also post a screenshot of what the GUI query looks like without a dynamic table.
Got it, thanks! It looks like this might not actually be handled by a single SQL query, but rather through multiple transactions handled by Retool. That suggests a way to do it via JavaScript (looping through a single query as described here). Or you might be able to do something like described in this StackOverflow post to do the update in a single SQL query. Both seem like they might be a bit of a doozy 
Either way, you're going to need an MSSQL resource with conversion to prepared statements disabled so that you can dynamically set the table name. If you don't already have that set it's recommended you create a duplicate resource with prepared statements turned off so that you can use it only where necessary to help protect against SQL injection.
If you're going for a single SQL query you might try something like the following:
BEGIN TRANSACTION
{{_.map(table1.changeSet, (row, i) => `UPDATE ${YOUR_DYNAMIC_TABLE_NAME} SET ${_.map(row, (value, key) => `${key}='${value}'`).join(", ")} WHERE WB_UNIQUE_ID='${table1.data.WB_UNIQUE_ID[i]}'`).join(';\n') }}
COMMIT TRANSACTION
That does quite a lot, we're trying to map over each set of updates and create something like
UPDATE table_name SET column1 = value1, column2 = value2 WHERE WB_UNIQUE_ID=id_from_your_table;
Using the _.map function and some template literals lets us get the basic idea:
_.map(table1.changSet, (row, i) => `UPDATE ${} SET ${} WHERE WB_UNIQUE_ID='${}'`)
There are 3 places we're using ${} to pass in some dynamic data in that:
This you can just pass directly from wherever you're pulling the table name, not that since the whole statement is getting wrapped in {{}} you don't have to do it again here
2. The values to update
This can be a somewhat finnicky string to build, we'll be using more mapping and template literals here. _.map(row, (value, key) => ${key}='${value}') creates a row of strings that look like key='value' we can then join them together with join(", ") so that they become key='value', key='value', key='value'
3. The id of the row
Since we're pulling updates from changeSet only the columns that are actually updated are present, so we'll use the i declared in the mapper to grab the id of the row you're updating table1.data.WB_UNIQUE_ID[i]
Put that all together with another join(';\n') to stick the lines together and you should have your query!
I know it's quite a bit to go through, you should be able to copy the query posted above and just replace YOUR_DYNAMIC_TABLE_NAME with the appropriate value. Let me know if that works!
I'll also file a feature request for dynamically specifying table names with MSSQL and let you know here if it gets included.