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
    • 1 yr 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
    • 1 yr 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
    • 1 yr 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

    • John_Halls
    • 1 yr ago
    • Reported - view

    How is Solde Interventions calculated?

    • IDAO
    • 1 yr 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
    • 1 yr 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
    • 1 yr ago
    • Reported - view

    Yes it's the sum from another table :

    sum(INTER.'Durée Interventions')

    • John_Halls
    • 1 yr 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
    • 1 yr 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
    • 1 yr 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
    • 1 yr 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
    • 1 yr ago
    • Reported - view

    It didn’t change today !

    It works !

    Thank you once again 

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 12Replies
  • 110Views
  • 3 Following