Let’s say I have a string value “ABC” that I want to try and find in a table “Table 1.” For the purposes here, let’s say the table is just one row with many columns.
In another table “Table 2,” I want to run a query or lookup or whatever function I should use and then return the column header text as a string when it finds the string “ABC.” How can I do that?
Now, let’s also say that the string “ABC” is called from a particular column “Find me” in “Table 2” where we want to basically run something like “find the value from column ‘Find me’ from this row in ‘Table 2’ and return the value of the column.” How can I do this?
I thought it wasn’t possible after looking into the options but figured I would ask.
I am developing an app for a week-long conference where the attendees will be broken up into multiple groups and then selecting leaders for various positions. We will be having elections to pick who these leaders are. Since we have around 1000 people attending and I want to include a couple chat functions that will write to a table, I was trying to avoid making a ton of extra rows for each position and eat away at my row total. Granted, we’re talking a max of about 100 positions versus 50k to 100k rows.
I already have a solution that works but wanted to see if I could simplify it. I’m thinking I could change my data structure so that one row is basically “results” and each column is a different leadership position, then the other table has a column that runs a lookup on the attendee’s ID across the whole row. A second column in the other table would then need to look at whether or not the attendee’s ID was found in a particular column and return an “if-then” string for each possible result. Annoying to write out but it works in just a couple columns.
Just based on your description, I think I would do something along the following lines:
Create a separate groups table, with one row per group. Use a multiple files column to store an array of UserIDs (the members of each group). And perhaps another column to specify the leadership role name for that group (if that’s applicable)
In your Users table, add a simple boolean column that identifies if a user is an elected leader.
A relation from your Users table that matches the UserID (RowID) with the UserID array in the Groups table will link each user to their assigned group, and you can use a lookup through that relation to retrieve the leadership role name.
Good chance I may be way off the mark in terms of understanding your goals, but perhaps the above might spark a few ideas.
Yes, all 1000 attendees will be signing into the app.
There will be up to 20 groups, with multiple leaders in each group (each leader has different jobs). Let’s say for this discussion that each group of 50 attendees will pick 10 people to fill 10 specific leadership roles.
Right now, I have a main Users table with all 1000 attendees and a separate Leaders table. The Leaders table has 20 rows, one for each group, and 10 columns for the 10 roles.
What I would like to do is have the Users table just have 1 or 2 columns that can query the results from the Leaders table and add the string name of the leadership role into it. For example, let’s say the Leaders table has a Timekeeper role and I have put the ID of the appropriate attendee in that column for each group within the Leaders table. How can the Users table query the whole Leaders table and return the string “Timekeeper” without having to add an individual column for each leadership role in the Users table, with one specifically being marked as “Timekeeper?”
If it’s not possible then I can stick with my current solution.
You could then pull that into your Users table through a relation to the Groups table, and use a bit of JSONata or JavaScript to determine each users role (if they have one). And you could do away with your Leaders table altogether.
The thing is, I have an election system built into the app where the attendees can actually see the list of candidates for each position and then vote electronically. Later, there’s a separate page for submitting the results. I would have to eliminate all of that.
I don’t think that you would have to eliminate it, but you may need to re-engineer it a bit. Totally up to you to decide whether or not it’s worth the effort. As you say, you already have a working solution. If the App is only going to be used for a short time, then it’s probably not worth the trouble of trying to optimise it.
So in my Group Data table, I can roll up all of the roles into one JSON column and then I have a Lookup in my Users table that pulls in that JSON data. But now I need to make a JSON Query column that will pull out the values I need.
Each attended is given a particular Attendee ID number. All of the data in the Group Data table is assigning roles with that number. So the JSON might show {“Leader”: 101, “Scribe”: 205, “Timekeeper”: 503} or something similar. And the Users table has a column for each attendee that lists their ID. How do I make a JSON Query to pull out the role “Leader” for the row that has 101 under the Attendee ID column? If I can figure this out, then I can have the Users table report everyone’s position with a single column.