0

Date of value specific changing

Hello ! I'm looking for how i can get the date of when a value is becoming negative. I'd tried this code : "if 'Value' <= 0 then today() end" but the function today() returns the date of the present day (and it changes every day lol). But i only want to get the date the first time the value becomes negative. I can't find a solution. Thanks in advance

12 replies

null
    • Lars
    • 2 yrs ago
    • Reported - view

    Hi IDAO,

    you could search for all records with a negative value, sort them by date (given you have a date field in the record) and then take the first one. If you don't have a date field, you can use _cd. 

    first(select Table where Value < 0 order by _cd)._cd

    (replace _cd by the name of your own date field if you have one)

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Instead of using a formula field, use a date field with a Trigger after update thus 

    if Date = null and Number < 0 then
    Date := today()
end

    if the number you are testing is a formula using a child table then the trigger has to be on the child table (the pace where the data is actually changing) and it becomes 

    if Parent.Date = null and Parent.Number < 0 then
    Parent.Date := today()
end

    Regards John

    • IDAO
    • 2 yrs ago
    • Reported - view

    Thanks for your answers !

    I've tried, but it seems that it doesn't work.

    To give you more details look at the screen shot.

    I sell some technical assistance contracts (with a specific duration of work)

    A contract has got a start date and an end date.

    I would like to fill the end date only once, when the number of remaining hours is ≤ 0 (last column) and it doesn't have to change after.

    Thanks

    Capture d’écran 2022-04-07 à 14.13.03
    • John_Halls
    • 2 yrs ago
    • Reported - view

    How is Solde Interventions calculated?

    • IDAO
    • 2 yrs ago
    • Reported - view

    Like this : 

    'Total Heures' - 'Cumul Interventions‘

    It’s a function whitch returns a duration : the total of number of hours in the contract less the number of hours of work already done.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Is Cumul Interventions the sum of hours from a child table linked to this table? If so, what is the link field called on the child table?

    • IDAO
    • 2 yrs ago
    • Reported - view

    Yes it's the sum from another table :

    sum(INTER.'Durée Interventions')

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Thanks. The Trigger after update has to live in the Inter table. The reference field INTER is the parent looking at the child. It will have an equivalent field from the child looking at the parent. If you can tell me what that is called I can give you the code you need.

    • IDAO
    • 2 yrs ago
    • Reported - view

    I hope i've well understood what you ask.

    Here the relations between the tables in my data base

    Indeed table INTER is child of table CONTRAT

    And the field they have in commun is 'N°Contrat'

    I hope this is the answer you expected.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi. I would suggest putting this code in the table level Trigger after update: of the INTER table. 

    if CONTRAT.'Date Cloture' = null and CONTRAT.'Solde Interventions' <= 0 then
    CONTRAT.'Date Cloture' := today()
end

    You will need to include the accents used in the field names

    Regards John

    • IDAO
    • 2 yrs ago
    • Reported - view

    Yeah !!

    It seems working

    I'll telle if tomorrow the date is still 08/04 and not 09/04

    Thank you so much for your help !

    • IDAO
    • 2 yrs ago
    • Reported - view

    It didn’t change today !

    It works !

    Thank you once again 

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 12Replies
  • 121Views
  • 3 Following