Interact with automatic assignment of field types for tables

Feature Requests
1

Goal: Understand how field types are initially assigned in the application for tables, and also if there is anything that can be done to interact with this assignment (and if/when Lodash is applied to the source data).

Not the goal: Learn how to change types, etc. once a table is populated/created. That is all perfectly clear.

It's pretty simple to see how dates and datetimes are assigned to field types. Same with URLs. But how/when Retool chooses to assign a field as a "tag" type field isn't clear to me. I can guess that it has to do with a limited set of consistent values coming through for the field, but only a guess. Sometimes this is great, sometimes this is a pain, as I need to then go and cleanup the type and the Lodash formatting that gets applied (sub-question - can I turn this off/interact with when Lodash is applied too?).

If this is already available, awesome! Please point me to in. If this is not necessarily explicitly available but can be coerced, that would be fine in the short term, but having clear controls over how this feature works would be helpful.

Thanks!

2 Likes
2

Hello @jg80!

Great question, I just asked internally and will hopefully come back with more info as to when tables will assign column data to be tags when auto-building as I am curious about that as well.

Also asked about lodash to find out more about where/when we use it and if it is possible to add additional controls/toggles to it! Will keep you posted on what I hear back.

1 Like
3

Hi @jg80,

So the column types from imported data/schemas is determined by an algorithm that uses various heuristics to "guess" the best type.

Is there a commonality with the type you want the data to be that is repeatedly being changed to tags?

In my mind, if there is an array of strings the table will probably guess those are tags. But wanted to check with you if there are more details on what is being incorrectly set to tags.

Also was curious about what formatting lodash operation is needing clean up? I can further press on where lodash is being applied and how/why is it being applied.

Is it the formatting for just tags that is an issue? Does this formatting need to be manually changed as well with the type changed from tag to other type?

There is a bulk editor for modifying multiple columns and their types quickly, it is the greyed out button to the left of the refresh double spin arrow in the picture below

Screenshot 2024-07-23 at 11.52.43 AM
Screenshot 2024-07-23 at 11.52.43 AM955×563 43.8 KB

4

First of all, the bulk editor is hugely helpful. My ideal state is to only click into columns for more complex controls, like adding events and options lists (but see below). For a simple "here's the data so you can look at it and maybe select a row or two" table, the bulk edit is much more efficient.

The bulk editor would seem the logical place to add some of the capabilities I am suggesting. For example, if each column's "Mapped Value" would show up in the editor, it would be more straightforward to cleanup/remove any Lodash (or maybe instead of/in additional to LoDash code have a set of pre-defined options like "All Caps", Capitalize First Word", "All lower-case", etc.) without having to click into lots of different columns and click back out again and all that.

The root use cases that brought this up for me

(1) I often have the same "enumerated values" relationship from a DB used across multiple tables. I prefer to have the FK as the field in the table data and map it to a (usually cached) set of values for the label. In this event, having the field automatically set as "Tag" is useful as it gets me half of the way there with the mapping. However, I still don't prefer the Lodash because depending on the label values it can still get a little messy visually.

(2) Sometimes I just want to display the mapped value and I'm not really doing anything with it like passing the FK to another query or an update. In that case, having the values show up as tags (which they typically do) and having Lodash applied is anti-useful. If I don't want it to be a colored pill, I need to change the setting to String, or change the color of the pill to 00000000. And the Lodash is only useful if the value is not stored as it should be displayed, which is less common in my use.

My ideal solution

Have the ability to set up the app with a global field setup for (some or all) of the tables used in the app (still allow changes table by table) such that (1) whenever the field tbl.foo_fk is used in a application table, the field is automatically set as a tag with correct mapped options (enum.foo_pk for value, enum.foo_value for the label and also the colors, etc.) from the table query that has the PK, and (2) for a defined set of fields, never assign them as tag or apply Lodash.

Thanks for taking a look at this.

p.s. Would also be nice to be able to edit column names directly from the bulk editor

5

I love the great feedback @jg80!

Just sent this over to an engineer on the UI Building team to dial in the feature requests.

Just wanted to get a few more details from you! Could you elaborate a little more on ' enum.foo_value for the label and also the colors, etc.) from the table query that has the PK' ?

My rough breakdown of the features requested is below, let me know if there's anything I am missing or can better explain/elaborate on to make it easy for our eng team!

Request breakdown:
-Inside of bulk edit window:
---display each columns "mapped value", help to cleanup/remove any Lodash formatting
---predefined options such as "All Caps", "Capitalize First Word", "All lower-case"
---Edit column names

Proposed Features:

-set an app to have a 'global field' for some or all tables used in the app, such that tbl.fk is used and auto set as a tag with the correct mapping options of enum.pk is the 'value' and enum.value is the label and also correct colors

-for a defined set of fields, never assign them as a tag or apply Lodash

6

Hi again @Jack_T - I think your breakdown captures it.

The overall concept is that there is a table enum:

foo_pk foo_value
1 Red
2 Blue
3 Green

This enum is used by two other tables (fruit_table and veggie_table):

fruit_pk fruit_value foo_fk
1 Apple 1
2 Blueberry 2
3 Peach 1
veggie_pk fruit_value foo_fk
1 celery 3
2 radish 1
3 lettuce 3

In the UI, whenever I create a table from fruit_table or veggie table and I include the field foo_fk, the table automatically creates a mapping to enum.foo_pk for the value and enum.foo_value for the label. Presumably I would need to have a select * from enum query in the app that would be used as the reference. I can then easily access the FK value for subsequent queries, or access the diplayedValue() for other uses.

While this association could perhaps be based on constraints info from the DB, it should also allow setup & extension by the app builder (for example, add field for HEX color to be used in the mapping setup that is selected by the user of the app in the UI).

1 Like
7

Thank you so much for the amazing feedback and crystal clear example/breakdown @jg80 !!!

Just sent in a bunch of feature requests, breaking down all the great ways we can improve our tables based on your suggestions. Will circle back with any updates I hear :man_technologist:

1 Like