0

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

Screenshot 2021-07-08 at 10.56.12

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!)

Screenshot 2021-07-08 at 10.56.27

 

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

Screenshot 2021-07-08 at 11.25.31

cheers

Mel

3 replies

null
    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Forgot to say - this is the forumula script

    last(JobDockets.'Order Date-')

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    just had a thought

    I wonder if max(JobDockets.'Order Date-')

    I will try this and see how this works in meantime

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    So... using max does work fine !!!! -didn't think i could use this with a date

Content aside

  • 3 yrs agoLast active
  • 3Replies
  • 261Views