0

use yesterday's information in the table

Hello

  I need some information from yesterday to automatically pass to the next day. I have a formula that does it all, but there is a problem, it changes all previous days, that is, the information is not saved, it changes every day

let c := last((select 'CASH DESK') order by number(Id));
let bc := last((select 'CASH DESK' where number(Id) < c) order by number(Id));
first(select 'CASH DESK' where Id = bc).'CLOSE 5000'

'CASH DESK' - table name 'Close 5000' filed name

how to make sure that the information of other past days does not change. Thanks in advance everyone

7 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    What happens if you replace the first line by:

    let c := number(Id);

    Cannot the last line be simplified as:

    bc.'CLOSE 5000'

    • iliper LTD
    • iliper_LTD
    • 2 yrs ago
    • Reported - view

    thanks Alain Fontaine, it works

    • iliper LTD
    • iliper_LTD
    • 2 yrs ago
    • Reported - view

    Full formula 

     

    let c := number(Id);
    let bc := last((select 'CASH DESK' where number(Id) < c) order by number(Id));
    first(select 'CASH DESK' where Id = bc).'CLOSE 5000'

    • John_Halls
    • 2 yrs ago
    • Reported - view

    I know Alain has found a solution for you but I'd like to suggest something.

     

    If I were doing this I would have a relationship joining yesterday to today created in the Trigger on create section. That way yesterday's figures will always be at hand for today, for example Yesterday.'CLOSE 5000'

     

    Regards John

    • iliper LTD
    • iliper_LTD
    • 2 yrs ago
    • Reported - view

    Hello John
     

    thanks for prompting.  I was originally given the formula at the webinar. But it did not work quite correctly, so I wrote here. I'm not a programmer and I need a complete formula so that I can copy it)

    Not long ago, at the vubinar, they gave me a formula that gave meaning now, in "time" field, time, but it displayed time an hour ago, could you give me the exact formula?

    Thanks in advance

    • John_Halls
    • 2 yrs ago
    • Reported - view

    I created a tabe called DaisyChain and made a table reference to itself, one half called Yesterday and the other Today

     

    Screenshot 2021-06-23 at 13.11.10

     

    I included a field called Flag. Every record except the latest has been set. We can then search for the only empty one to get Yesterday's record. But in the Trigger on create we also need to briefly set the current records flag before searching, and set it back to empty again. The script links Yesterday to Today.

     

    Screenshot 2021-06-23 at 13.12.06

     

    Once you have this working you can access any field from Yesterday using the syntax Yesterday.FieldName, and even the day before with Yesterday.Yesterday.FieldName. Running totals and 7 day summaries, etc all become very easy.

     

    Screenshot 2021-06-23 at 13.17.49

     

    Hope that gives you some ideas.

     

    Regards John

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Always post that litte bit too soon. Just realised the RunningTotal isn't correct. This might have to be scripted but still pretty easy to get going.

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 7Replies
  • 408Views