Direct data type support for arrays in tables

Product Suggestions
,
1

Introduction

I found similar discussions but none were exactly what I wanted so hopefully this isn’t a duplicate request.

I could really use the ability to store arrays in Tulip tables. Specifically I want to be able to use queries with a “contains” requirement, but instead of the current method where you can get multiple matches due to repeated characters, I want an exact match with an array element.

This would allow me to run a query + aggregation to collect all the records that apply in a very flexible manner…

Use Case

We are working on eDHR which includes managing all inspection requirements. I’m trying to minimize the maintenance burden by allowing one set of records cover a whole family of parts BUT we need to be able to allow for some variability in requirements such as, “a longer part has more features to check”. Or “some features don’t exist on some part sizes”.

My current method is to use a single text field to list the size of parts that are required for that inspection such as:

12,14,16,18,20

But this is very fragile. The choice of a , for a separator is meaningless for the query, it just makes it more human readable. Also a query for contains 12 would also match 120 or any other part of the string containing a 12.

2
  1. I do think that this could also be solved with stronger support for table joins and linked records. In a proper database you wouldn’t want to store arrays in a table, you would want a one to many relationship to another table.
  2. However, adding the ability to store not just arrays but objects in tulip tables would be extremely powerful for a myriad of other uses and this one. And this is probably more likely than a full suite of improvements for linked records.
  3. For posterity with the contains issues-- this comes up in a few random ways-- my workaround in the past has been to use “12”,“14”,“16”,“18”,“20” and then search for records which contain “12”.
2 Likes
3

Yes, or simply add a , at the beginning and end as well and include it in the statement:
Contains: ,12,

4

I ended up just making them actual JSON arrays stored as a Text datatype which means I can parse them into arrays in the app if needed and it prevents accidental matches as well if I don’t.

[“12”,“14”,“16”,“18”,“20”]

1 Like
5

As far as I understood you could use two tables and a many-to-many record link to solve this problem.
Table 1

Inspection ID connected parts
Inspection 1 Part 1, Part 2, Part 3
Inspection 2 Part 2, Part 4

Table 2

Part ID Inspection ID Property 1 Property 2 …
Part 1 Inspection 1 … … …
Part 2 Inspection 1, Inspection 2 … … …
Part 3 Inspection 1 … … …
Part 4 Inspection 2 … … …

In your app you would need to interactive tables. One table displays the possible inspections and the other table would display the required parts and their properties. By selecting an inspection you would filter the parts table for Part ID is in connected parts.