Running queries after setting temporaryStates

App Building
, , ,
1

Hi there :wave:,

I have 3 modules.

  1. JavaScript
  2. Temporary State variables
  3. SQL variables

With the JavaScript code I fetch updated rows from the table and extracts some columns from the table. This part is done from the JavaScript.

After extraction I set extracted values into temporary states these states will be further used into SQL statements.

After setting variables, I just trigger the SQL query using .trigger()

but when I run my javascript code it seems it's not setting temporary variables, because my SQL queries uses old values, not using new values which I just set into temporary states.

Here is my JavaScript code which I'm using -

var recordUpdates = table1.recordUpdates[0];
// remove

var state_name_underscored = recordUpdates['Custom Column 2'];
var city_name_underscored = recordUpdates['city_name_underscored'];
var country_name_underscored = recordUpdates['country_name_underscored'];

temp_city.setValue(city_name_underscored);
temp_state.setValue(state_name_underscored);
temp_country.setValue(country_name_underscored);

filter_table_sql.trigger().then(
  () => {
    table1.setData(filter_table_sql.data);
  }
)

And the SQL query which I'm using -

SELECT * FROM retool_tst.xct_lookup30_institute_name_state_name_va_flat
WHERE state_name_underscored = {{temp_state.value}}
AND country_name_underscored = {{temp_country.value}}
AND city_name_underscored = {{temp_city.value}}

These are temporary states -
image

I'm just trying to make my SQL use new values which I set using JS instead of old values.

Some data which might be useful in table1.recordUpdates

[{'Custom Column 2': 'new_york',
  'city_name_underscored': '',
  'country_code_underscored': 'us',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': '73a5c56b-f189-4342-9365-84ae14d68fcf',
  'institute_name_final': 'veterans hospital korea veterans health care',
  'institute_name_grouped': 'veterans hospital',
  'institute_name_new': 'veterans hospital korea veterans health care',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': '',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''}]

some data in table xct_lookup30_institute_name_state_name_va_flat

[{'city_name_underscored': 'washington',
  'country_code_underscored': 'us',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': 'e6bbfe7e-c062-4315-8f61-4d19432b165c',
  'institute_name_final': 'veterans affairs and george washington university '
                          'medical center',
  'institute_name_grouped': 'veterans affairs',
  'institute_name_new': 'veterans affairs and george washington university '
                        'medical center',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': 'virginia',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''},
 {'city_name_underscored': 'washington',
  'country_code_underscored': 'us',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': '29bbf938-0dfa-450f-bfa5-2293aee64d54',
  'institute_name_final': 'veterans affairs and george washington university '
                          'medical center',
  'institute_name_grouped': 'veterans affairs',
  'institute_name_new': 'veterans affairs and george washington university '
                        'medical center',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': 'west_virginia',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''},
 {'city_name_underscored': 'washington',
  'country_code_underscored': 'us',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': '1bdffece-5014-43f7-bfdf-183ee9c44f9b',
  'institute_name_final': 'veterans affairs and george washington university '
                          'medical center',
  'institute_name_grouped': 'veterans affairs',
  'institute_name_new': 'veterans affairs and george washington university '
                        'medical center',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': 'wisconsin',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''},
 {'city_name_underscored': 'new_york',
  'country_code_underscored': 'us',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': '7ae565c8-a0bf-452b-aef1-096ae1e1d2f4',
  'institute_name_final': 'veterans affairs westside medical center',
  'institute_name_grouped': 'veterans affairs',
  'institute_name_new': 'veterans affairs westside medical center',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': 'new_york',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''},
 {'city_name_underscored': 'new_york',
  'country_code_underscored': 'tw',
  'country_name_underscored': 'united_states_of_america',
  'entity_type': 'va',
  'id': 'a2d25249-c3b1-4c9b-a107-65f07bd470d9',
  'institute_name_final': 'veterans general hospital yuanshan branch center',
  'institute_name_grouped': 'veterans general',
  'institute_name_new': 'veterans general hospital yuanshan branch center',
  'is_active': 'true',
  'notes': '',
  'ringgold_id': '',
  'state_name_underscored': 'new_york',
  'update_dates': '2022-10-11T12:22:23.894253',
  'updated_by': ''}]
2

Check out this post, I was running into similar issues as well...

4

How does selecting 'Keep variable references inside the query in sync with your app' impact referencing updated values in JS queries?

The information you have requested is very important; please provide details about it. Thank you.

5

When 'Keep variable references inside the query in sync' is not checked on, the variable will be defined at the beginning of the query trigger and won't keep track of updates. This is more performant, but there are certainly cases where you might need to set the value and keep track of this change, which is why we expose the option in the advanced settings