Save per user table preferences

[itai-retool]

Background:

A user wants to save individual filters values and settings, instead of applying it every time he/she login into the app. In this guide, you will learn how to enable a "Save filters" functionality. When the users save their filters configuration in the app, it will be stored in the database for the next use.
The application owner will be able to track the commonly used filter value and settings and provide additional value to their user's community.

Instructions:

Clarification - In this example, the data is stored in Google spreadsheet. But it can be used with other datasources, using the same logic presented here.

1. Create a table called "User Preferences" that will contain the following info:
   

2. The user's filter values will be save in a JSON BLOB, Here's an example:
   [{"filter":"Company", "value":"Google"},{"filter":"Job Title", "value":"Engineer"},{"filter":"Office Location", "value":"San Francisco"}]

3. Once you have set the table, create an app with select filters and setting you are interested to save, and a "Save Filters" button.
   

   

4. Create the following queries for the "Save" Functionality-

   A. Delete Previous Record in the users preference table (GSheet):
   

   

   B. Get current filters selection (JS Query) -

*This will retrieve the values from the select dropdown filers and structure the values into a JSON BLOB format.

    // components that should be tracked for user preferences
    let components = []
    
    components.push(select1)
    components.push(select2)
    components.push(select3)
    
    // create the user preferences
    let filters = []
    
    for(let x = 0; x < components.length; x++ ) {
      filters.push({
        label: components[x].label,
        value: components[x].value
      })
    }
    
    return JSON.stringify(filters)

C. Insert Or Update record to the users preference table (G-Sheet) -

                         {"userID": "{{current_user.id}}",
                          "name":"{{current_user.fullName}}",
                         "selectedFilters":"{{getFiltersJSON_FromUserSelection.data}}",
                          "lastUpdate": {{moment()}}}

*Every user has a single record, but we can also save history. Trade off- The table will have many records over time.

5. Create the following components and queries for the "Load" functionality -

   A. Get CurrentUserSaved Filters (G-sheet) - We will retrieve a record for the user who is currently using the app:

   

      let result = data.filter(row => row.userID = _.toString({{current_user.id}}) );
      return result
   

   B. Create a table "userPreference" that will present the data from the userPreference table, based on the previous query "CurrentUserSavedFilters.data"

   C. Populate Filters from JSON BLOB (JS Query) -
   Parse the values to allow it populate the select filter default values:
   

   

      return JSON.parse(userPreferances.data['0'].selectedFilters)
   

6. Set up the select filters default values to retrieve "userPreference" from the previous JS Query result. The result should have a single record (one per user)
   Example -
   
   {{populateFiltersFromJSON.data['0'].value}}

7. Set up the sequence between the queries - :

   • Click on "Save Filters" button
   • Trigger Click event - Delete Previous Record in the users preference table (4A)
   • Define for "Delete Previous Record in the users preference table" On Success to trigger "Get current filters selection" (4B)
   • Define for "Get current filters selection" On Success to trigger "Insert Or Update record to the users preference table" (4C)
   • Define for "Insert Or Update record to the users preference table" On Success to trigger a notification "Filters were saved." and trigger "Get CurrentUserSaved Filters" (5A) to refresh the userPreference table.
     
     

Attached:

• App JSON
  Demo -Saving User's State App.json (38.5 KB)

• Spreadsheet structure example
  User Preference - Sheet1.csv (239 Bytes)