0

Multiply a number to get a status

Hi ! How would it be possible to modify a loyalty status (which corresponds to a number) according to the sums spent.

Example: the threshold for changing loyalty level is every €600

- Case 1: a customer has spent €350, his loyalty level is therefore at 0 (by default).

- Case 2: a customer has spent €625, so his loyalty level must be 1 because he has exceeded the €600 threshold

- Case 3: a customer has spent €1470, his loyalty level must therefore be 2 because he has exceeded the levels of €600 then €1200

To achieve the calculation, I already have a formula field named "Chiffre d’affaires" which shows me the total amount spent by my customer. So I can start from there to calculate the loyalty level.

Is it possible for a formula to adapt the loyalty level (multiplier) by automatically calculating the ceiling every €600?

Thanks for your help

5 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    You can use floor('Chiffre d’affaires'/600)

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      RoSoft_Steven Thank you for your feedback, I will try tonight.

      Can I reuse this formula to show me the Chinese sign of a client?
       

      The Chinese sign is calculated by 12-year slot.

      Example: the sign of the tiger: year 1950 then 1962 then 1974… the sign of the rabbit: year 1951, 1963, 1975…

      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

       I have another question related to my previous request.

      I have a formula field named "Niveau de fidélité" in which I inserted the formula floor('Chiffre d’affaires'/600) and I have a yes/no field named "Carte cadeau de fidélité à envoyer" .

      I would like that each time the "Niveau de fidélité" field is increased (1 - 2 - 3 etc.), the "Carte cadeau de fidélité à envoyer" field is changed to yes.

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

       you would need some kind of trigger to do that and since a formula field isn't able to trigger or change any data field, you need a static field that holds the Niveau de fidélité and compare the formula field with this one. (So you would have two fields with the same value, one formula field and one static number field) On startup database you can check if the formula field value is higher than the static one, wich means there's an increase. With that, you can trigger the yes/no field then. To fill the static field in all the records, you can use a loop (for i in select table do.... end).

    • Fred
    • 1 yr ago
    • Reported - view
    Sébastien Guillet said:
    Can I reuse this formula to show me the Chinese sign of a client?

    Interesting question.

    You can but just going by year is not accurate as the Chinese New Year typically falls around the end of January to mid February.

    But a simplified method could be something like:

    let y := year(Birthdate);
switch true do
case [1982, 1994, 2006, 2018][= y] > 0:
    "Dog"
case [1983, 1995, 2007, 2019][= y] > 0:
    "Pig"
end

    A more accurate way would be to create two tables (Signs,NewYear). The Signs table would have 1 field called Animal. The NewYear table would be a child of Signs and have three fields (sDate and eDate) and a reference field to Signs (we can leave the default name Signs). The sDate and eDate fields would be the start and end date for the zodiac year.

    Then in your other table you can create a formula field with something like:

    let t := this;
let getYear := (select NewYears where sDate <= t.Birthdate and eDate >= t.Birthdate);
getYear.Signs.Animal
Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 74Views
  • 3 Following