# Filtering related records **URL:** \ **Category:** Ask for Help\ **Created:** [December 7, 2022, 12:22pm UTC](https://community.glideapps.com/t/filtering-related-records/55507 "2022-12-07T12:22:14Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Wayne\_S](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wayne_s/32/50714_2.png) [@Wayne\_S](https://community.glideapps.com/u/Wayne_S)\ **Post date:** [December 7, 2022, 12:22pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/1 "2022-12-07T12:22:14Z")
Hello I have a few tables in by solution and I’m trying to produce reports and exports ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/f/8f335e870ec95b8f1636b48eb095ff342d6c9b8e.png) In the screen shot, the OrderID column contains multiple values, I need to related/look up/match to a single value that contains the date that is in the Formatted Date column. Any idea how or even if I can do this? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 7, 2022, 12:30pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/2 "2022-12-07T12:30:53Z")
Can you provide a screen shot of the table that you are relating these records to? I notice that your first column (Date) contains the same value in every row. So you’re going to be getting the same result in every row - is that what you need? As a general rule of thumb, it’s best to first convert dates to integers before using them in relations. This can be done with a math column using the following formula: ```auto Year(Date) * 10000 + Month(Date) * 100 + Day(date) ``` That will give you an integer value in the form YYYYMMDD. I can see that you’ve used the Format Date column, but just be aware that can sometimes give inconsistent results. So it’s generally better to use the math column option. ---
**Author:** ![Wayne\_S](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wayne_s/32/50714_2.png) [@Wayne\_S](https://community.glideapps.com/u/Wayne_S)\ **Post date:** [December 7, 2022, 12:41pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/3 "2022-12-07T12:41:41Z")
The date value will change when more rows are added. It is the date the row is created. This is a usage table The other table contains a date which the order relates too, think of it as a use by date. So I need to see the order usage by use by date. There can be multiple usages of an order on a given date. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/8/382b70079150f3618e78cd6293a997bf574e7677.png) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 7, 2022, 12:50pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/4 "2022-12-07T12:50:36Z")
Okay. Looks like your first table doesn’t contain an OrderID? So you would need to do all this in your second table. Create a template column that combines your OrderID and Date, and then create a multiple relation that matches that column to itself. You could then do whatever rollups you need via that relation. But I’d still recommend first converting the dates to integers, and use that in the template. ---
**Author:** ![Wayne\_S](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wayne_s/32/50714_2.png) [@Wayne\_S](https://community.glideapps.com/u/Wayne_S)\ **Post date:** [December 7, 2022, 12:57pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/5 "2022-12-07T12:57:59Z")
Ok but I need the data from the second table in the first table if that make sense? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 7, 2022, 1:10pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/6 "2022-12-07T13:10:37Z")
Yeah, kind of - although I’m struggling a little to visualise the full picture. Anyway, in order to summarise by Date and OrderID, you need a template that combines both plus a self-relation. So if you want that in the first table, you need both values in that table. ---
**Author:** ![Wayne\_S](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wayne_s/32/50714_2.png) [@Wayne\_S](https://community.glideapps.com/u/Wayne_S)\ **Post date:** [December 7, 2022, 3:06pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/7 "2022-12-07T15:06:57Z")
Thanks Darren but I’m not sure I follow. So I’ve created the relation in table 1 by Row ID and Date as below: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/4/f4bab6a6372620ad225772a433033233cdee945d.png) And I now need to create a one to many relationship from table 1 to table 2. To do this, I think I need the get the Row ID & Date into table 2 to make the relation. This is where I’m struggling a bit ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 7, 2022, 3:33pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/8 "2022-12-07T15:33:18Z")
There will be a way, but at the moment I think I’m missing the larger context, so it’s a bit difficult to advise. Any chance you can make a loom video and talk through what you have and what your goal is? ---
**Author:** ![Wayne\_S](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wayne_s/32/50714_2.png) [@Wayne\_S](https://community.glideapps.com/u/Wayne_S)\ **Post date:** [December 9, 2022, 1:01pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/9 "2022-12-09T13:01:53Z")
I managed to get there Darren, by using a template column with the date and row id, looked this up from the other table, then I created a column on said table with the current date and used some spilt and join text functions to return the correct row ID based on the current date! Was a bit of a mind %$£$ but I got there in the end. One question however, I haven’t taken your advice (stupidly) and I have converted the dates to integers. So I may need to do it again with this in mind. What kind of inconsistent results might I get if I don’t do this? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 9, 2022, 1:12pm UTC](https://community.glideapps.com/t/filtering-related-records/55507/10 "2022-12-09T13:12:46Z")
> [@Wayne\_S](#): > > I managed to get there Darren, by using a template column with the date and row id, looked this up from the other table, then I created a column on said table with the current date and used some spilt and join text functions to return the correct row ID based on the current date! Was a bit of a mind %$£$ but I got there in the end. Nice work 👍 > [@Wayne\_S](#): > > One question however, I haven’t taken your advice (stupidly) and I have converted the dates to integers. So I may need to do it again with this in mind. What kind of inconsistent results might I get if I don’t do this? Well, from the very first screen shot you posted, it looks like you’re using the Format Date plugin to adjust the displayed date format. You’ll _probably_ be okay with that, but I have found that plugin can sometimes give inconsistent results, so I tend to avoid it. By inconsistent, I mean it depends on the users device, their regional settings, where they are in the world, the time of the year, and probably the phase of the moon 🤷‍♂️ I’d say it probably works perfectly fine 95% of the time, but that 5% when it doesn’t can do your head in if you’re trying to debug it. So I just find it best to convert the date to an integer in these cases, as I can be confident that will work 100% of the time with no nasty surprises.