Trying to use if-then-else to select data from column based on another column value. There is a column in my table called location_index and a collection of columns named loc_0, loc_1, loc_2, etc.
I would like to say “if location_index = 1, then loc_1”.
All my loc_X columns are multi-valued lookups. They don’t show up as selection options in the if-then-else column creation dialog. Other columns are available. Some of them are lookups. Also missing are multi-valued query columns.
Well, a workaround is to replace the Loc_X Lookup columns with Joined List columns which will return single value strings. Then run it through your IF column, followed by a Split Text column to convert it back to an array.
Queries and Relations don’t contain values. They are just pointers to related rows, and not a singular column in those related rows, so they should not be selectable in your use case. What you see in a Query or Relation is just a random column value to indicate that matching rows were found.
I tried the join-split idea. Column-wise it works great, but when I use the split column in a data table, it now behaves differently. When it’s a lookup column returning an array, the column fills down, as expected. When it’s a split column, it’s putting the entire array into the cells.
First, I want to say thank you all for your help. I can barely understand the logic of my own stuff, so how you can decipher other peoples stuff is amazing. I really appreciate the help.
Hopefully these screenshots will help.
Here is a simplified view of the source data table named rounded arcs. Only the 3 of the 4 columns shown are relevant to the problem - ER idx, ER start, ER speed and ER date. Given a date and a speed, one can identify a list of start times.
In my working table called select and go, I have a query column that filters rounded arcs by two input values formatted date and signed speed (date and speed).
Why aren’t the values from the underlying start column not populating down the table?
To clarify why I’m doing all this in the first place, there is more than one location and I need to select the ER start column as the start join column with an if-then-else, which can’t see lookups, but can see the single-value joined list columns. By the way, when I do add a look-up and link it to the data-grid, I get the same behavior.
Because all the values from the split (and your previous lookup) are all in one cell, but as an array of multiple values in that one cell. They do not represent rows of data even though they ultimately came from rows. Is your goal just to have a nicely formatted list of times? Would that list be clickable for more details, or just for viewing?
Super quick and dirty…get rid of the Split Text column and instead create a template column that takes the IF column as input and replaces commas with a carriage return.
The next idea is to get rid of the split text column, and instead create a javascript column that takes your IF column as input and creates a simple HTML table that you can display using a Rich Text component. Here’s a quick and dirty version from chatGPT to get you started.
function generateTableFromCSV(p1) {
// Split the input by commas to create an array of values
const values = p1.split(',');
// Start creating the table as a string
let tableHTML = '<table border="1"><tr><th>Values</th></tr>';
// Loop through each value and add a table row for it
values.forEach(value => {
tableHTML += `<tr><td>${value.trim()}</td></tr>`;
});
// Close the table
tableHTML += '</table>';
// Return the generated HTML string
return tableHTML;
}
return generateTableFromCSV(p1);
The more complex version would be to move some of your logic to the rounded arcs table. First create Single Value columns in the rounded area table to retrieve all of the values you need from the working table. Then create any IF columns that would mimic whatever logic you use to determine the same things you did in the working table. Ultimately you want to end up with a final IF column that only returns the specific column values from each row in the rounding arcs table. By moving some of that logic, you could have a collection that only displays the IF column value if that column is not empty. A bit more work, but gives you the option to display times as a collection instead of just text or an HTML table, and if for some reason you wanted the option to click on a time, it would take you to the appropriate row in the rounded arcs table.
