Hello
I have a few tables in by solution and I’m trying to produce reports and exports
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?
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:
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.
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.
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.
Ok but I need the data from the second table in the first table if that make sense?
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.
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:
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
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?
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?
Nice work 
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.
