I would like to be able to do a lookup from different tables based on the value of a column which relates to the table name.
Here are the tables and the relationships:
-
Data Type Table where the “table” column is the name of the table the value will be stored
-
Parameter Table with Data Type Id column relates to the Data Type table above
-
Parameter by Project Table where the Parameter Id column relates to the Parameter Table above and the Value Id column relates to the row in the Parameter String table below in this example.
-
Parameter String table example where the final value is written
The final result would be to get the “value” from the parameter_string table (Image 4) in the parameter_by_project table (Image 3) as a lookup or query or something. Is this possible to dynamically get tables and then values?
Thank you in advance for any help you’re willing to provide!
Not natively. My idea would be to create multiple query columns for each of the possible table name. Create lookups or joined lists. Then create if then else and return the right column based on the table name in the conditions.
It will be really complicated and not easy to maintain… What are you trying to do in the app? What is the flow? Maybe we could give you advices and recommendations on how to implement alternative solutions.
2 Likes
Thanks for the responses @MaximeBaker.
Here is a rundown of the app / process.
We are building a comparison app that will look at historical project costs and then use industry data to inflate those prices to today’s dollars.
-
Create a Comparision
A. Assign two or more projects
B. Assign Factors that will affect the cost
-
Assign Parameters to each Project with various data types and not all projects have the same parameters or values.
A. Project 1 may have a Boolean, String, and Double
B. Project 2 may have an Integer and Text
-
The way the Parameters are set up is such they are just Key / Value pairs essentially based on that data type.
-
Then when I want to call the values (Relation / Lookup) the table they will come from will be dynamic based on the data type and the parameter which changes often.
Honestly if this isn’t a very easy thing to do, I am working with PGSQL so I can just make a view with the info, just means a lot more hoops for all the CRUD stuff, but I have done it before.
I mean yeah, if you want to keep the data structures that way (good relations by the way, I like generic things) you might want to do it from SQL. It is possible from Glide tho. But if you want to reuse these views, then you should create them in the database. The only thing that would not work is that I don’t think you can use SQL views in Glide. Or is it ? With read-only table queries? @ThinhDinh
I haven’t used SQL at all in Glide so gonna sit this one out.
Found it! Yes it is possible!
@Sean_Page You can use a custom query to call your view!
1 Like
Thank for the follow up @MaximeBaker, I did end up going with a View from Postgres. I have done this successfully many other times, but was hoping to need all the extra hoops this approach brings with it.
Did you create a custom query from Glide to get data from that view?
1 Like
No, you can load a view just like you do a regular table from Postgres so I created it in the actual DB so I would have access to it for other applications like PowerBI as well.
Then if you use a single relation to any “real” tables you can use any normal Data Operation on that as the “Item” when editing or adding.
What would happen if you edit data from Glide on the view? I don’t think it will work… I don’t know postgres a lot, but I know that editing data from view in MSSQL is not possible…
You cannot directly update a view in SQL. However, you can update the underlying table data that the view references, and the changes will be reflected in the view when queried.
That is why I talked about the read only custom query.
1 Like
Correct, you do not edit the View, but that is why you put the relationship column to the table you do want to edit. Then you can pick that item to edit, update, or delete etc.
This is the first pain that you now have to do everything somewhere else, and the other one is now every operation you make needs to call a Reload Query of the View.
These things aren’t major obstacles, but something to be aware of.
You mean that you can delete a row from a foreign key? It allows you to do that? 
From Glide?
1 Like
Yes. You can delete single or multiple rows through a relation or query.
1 Like
Yes, I do it quite often as views are much easier to get the data that I want for Filtering / Grouping since I can’t do that with Lookup columns. If we could use Lookups for those things I would use significantly less views.
I mean from a custom query in read only.
