I’m trying to make a relation between the row ID of one table (table 1) and an if then else column in another table (table 2). when i make the relation in table 1, it doesn’t work and all the cells are blank. when i make it in table 2 in the reverse order, though, it works. Does anyone know what could be causing this?
I assume you are only expecting a match for the rows that have ven-001 based on your screenshots?
I can also tell by the grayed out rows that you have row owners applied. Are you trying to match to a row that the user you are previewing as doesn’t have access to?
What kind of data sources are you using? Glide Table, Big Table, Other?
What does the configuration for your other relation look like?
- For this particular example, yes, but the other row ID values will get filled in based on user inputs.
- Yes, I just started using Glide so I’m not sure what row owners is or how to enable it, but the row I’m trying to match to is the ID that’s automatically assigned when a row is added in Table 1 (Row ID) to the column in table 2 that inputs the Row ID when a user fills out a form.
- The data is from a form in Glide
- please see the picture below
It appears to be because it doesn’t like that I’m using a (current date/time - submitted time) calculation in my if then else column. When I set it up so there is no date/time calculation, it works as intended, but when the date/time is involved it doesn’t work. I think it works when I make the relation in table 2 because that is where the date/time calculation is occurring and when it’s the other way around it doesn’t like pulling that data from another table. Do you know if there is a potential workaround to this?
The current date/time is a constantly changing value, so that’s never going to work in a relation, which attempts to exactly match two values.
Can you explain at a high level what your end goal is?
It could be that you need to use a query rather than a relation.
So I created a form that allows users to rate something 1-5 and also gets the time they submitted the form at and which “Row ID” it relates to in table 1. I want to filter out the forms that were submitted more than 2 hours from the current time. I set up an if then else to filter out the old forms by only displaying the “Row ID” value for forms less than 2 hours old and leaving the rest blank. But when I try to use that column in the relation to table 1, it doesn’t work
I wouldn’t bother with a relation.
I’d do it like this:
- Create a math column using the following formula:
Now - 2/24(this will give you the date/time 2 hours ago) - Now create an if-then-else column:
– If Submission Date/Time is before 2 hours ago, thentrue - Use the if-then-else column as a filter (only show rows where it is checked)
You could even skip the if-then-else column if you wanted to, but personally I find it better practise to keep filtering logic inside the Data Editor.
I set it up as you described but I switched the if-then-else column to be true if submission date/time is after two hours ago. When I create the filter, though, no rows show up when the filter is set to “is checked” but when set to “is not checked” it leaves all rows, even the ones with checks. I also tried it with it being true if submission date/time is before two hours ago and same result. Please see the screenshots below.
Also, I should explain what my true end goal is. I want to take the ratings that were submitted with the form and stored in table 2 to be put into an average rating for each Row ID that is in table 1 and that average rating will be displayed with its corresponding row ID in the layout. I’ve gotten it to work for all ratings that were submitted, I’m only having trouble trying to filter out the 2 hour or older ratings.
Try ‘On or After’ (which accounts for time) instead of ‘Is After’ (which only considers days, ignoring time).
edit: I might have that backwards
Show your math column. Trying to understand where you are calculating 2 hours.
Changing to the Now value fixed the relation in table 1, thank you guys!
I lied, actually. My relation only works if I use “On or after” instead of “is after”, but when I do that, it doesn’t clear out the reports older than 2 hours as I intended, presumably because it only considers days, as Jeff mentioned. Do you know why this is or if there’s a workaround?
I feel like I’m missing a piece of the puzzle that explains why you are having problems, but try flipping your thought process a bit. Instead of calculating 2 hours from Now, add 2 hours to your SubmittedAt column instead. Then change your IF column or filter to compare where the math value ‘Is After’ Now. It might be a lot less calculation intensive because it won’t be recalculating the math column every 10 seconds when Now updates. Instead, each math cell will calculate once against the submitted date and then compare against a single Now value instead of hundreds of Now values that are continuously changing.
That appears to have solved it! I think you’re right that the constant calculations with the now updates made it not work. Thanks again for the help!