Last (or First) function breaking down?
I have a slight issue with getting the last (or first) job date from a child related table
Parent table is 'Customers' - Child table is 'JobDockets'
So I have a view that shows all the jobdockets in reverse jobnumber order
at the top i also have a formula field (last print job) to show tha last date that a job was ordered by a customer (It normally sits on the main form - but i have just moved it onto next tabbed form to show you here!)
for most customers - the formula works okay and as I click on each customer it changes to reflect the correct last date a clustomer ordered -
However - Where a customer has serval jobs last date shown in the formula field is (sometimes) going wrong and getting an ordered date from the middles of the jobs done?
I have tried both with last and first and it keeps going wrong. Yet if i log out and in again (i'm on web version) it repoints correctly but only for a while.
Ninox states it gets the first or last value according to Ninox's internal sorting
my jobs are always added into the database in order (and the next number for a job bag is auto assigned and thus the data orderd is always todays date on that date - so to speak
The formula field by defininition is a vitual field and as such recalculates every time and as it is having to this for every client (approx 1400) and sort and return every last date of the jobs for each client - variable amount but total approx 11,000 jobs in all approx. I still think these are low figures for memory sort issues.
Can anyone suggest a better approach ie that i could improve the formula etc ?
I seem to remember somewhere about sorting on server side (but alas i can't find this again)
This is the same above records a few minutes later - as you can see the lastdate in forumla field is picking a date in middle of the jobs list
cheers
Mel
3 replies
-
Forgot to say - this is the forumula script
last(JobDockets.'Order Date-')
-
just had a thought
I wonder if max(JobDockets.'Order Date-')
I will try this and see how this works in meantime
-
So... using max does work fine !!!! -didn't think i could use this with a date
Content aside
- 3 yrs agoLast active
- 3Replies
- 261Views