Hello Gliders. I am pulling a row from table A to table B through a relation. That row in table A has a DATE field. In table B, I am pulling that Date field through a LOOKUP because I want to use a filter for a graph (date is after x and before y). However the lookup field does not work for that. What can i do? Can i easily convert that lookup data into date format?
Is the relation a single or multiple relation?
Can you show us the lookup column in the Data Editor?
Yes, that’s the problem. A lookup through a multiple relation returns an array. What you need to do is change it to a single relation.
Thanks Darren!! When the relation is single, the lookup works for filters! However, the relation needs to be multiple. Is there a workaround?
Yes. You could either create two relations, or you could use a single value column to extract the first date from the lookup column.
I am not sure I get what you are saying. If the relation brings multiple rows, I need to include in the graph all of them that satisfy the date filter.
But if you are pulling multiple rows with the relation, and only need one single date from those related rows, what are you expecting or trying to do with it? Can you show how you are configing the chart and your relation and lookup? Is the relation being used as the source for the chart?
OK. The chart points to table B. Table B has 10 rows because there are ten statuses where a project might be in. Thus, I can have many projects in one status. The radial chart shows the sum of the project values per status. Does this help? But the user might want to see the chart only for projects that satisfy a date range, say only Q1. That is why I need to somehow get to all project dates.
How are they applying the filter? Are you using the in-app filter feature or building your own filter?
Building my own filters. The user chooses a begin date and end date which gets written in the user profile table. Then those dates get compared to the project dates in the filter section of the radial chart.
OK. Apologies if I’m way off because I don’t quite have a full picture of your setup, but I think I’m starting to understand.
I think what you should be doing is switching your relation to a query. Then you can apply your filtering directly in the query column based on your user selected dates. That way you won’t need to filter the chart or do anything with a lookup column.
No apologies accepted
It is amazing to have you experts as a resource and I can only be grateful for your continuous help and time invested. Thank you sincerely.
The QUERY worked !! Thank you very much!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.