0

Help with a Formula in a list view

Hello!
Could you guys help me with a formula?

I have 3 tables:
appointments, treatments, products.
Each related to another: 
each appointment can have several treatments, 
each treatment can have several products that are used in that treatment.

In a list view of Appointments table i made a formula column, that calculates the comission of my employee. It checks the product type, and depending on it makes a calculation:

   if Treatment.Product.Type = 5 then
      Paid * 0.25
   else
      Paid * 0.15
   end

The formula works fine when there is only one treatment in an appointment, but if there are 2 treatments or more, it ignores the "if Treatment.Product.Type = 5" and uses the option "Paid * 0.15".
Even if both threatments have Treatment.Product.Type 5.

I guess it has to use a loop or something to go through each treatment to calculate the comission for each and then give a sum. But i dont know how to acomplish it.

Help me please! )
 

5 replies

null
    • Fred
    • 1 mth ago
    • Reported - view

    Try:

       if contains(numbers(Treatment.Product.Type),5) then
      Paid * 0.25
   else
      Paid * 0.15
   end

    You are correct, the moment you have 2 or more treatments then Ninox will return an array. To make things clear, we start with the numbers() command to tell Ninox to return the choice number of the Type field. Then we use the contains() command to compare the number 5 to the array and if it exists then.

      • Agassi
      • 3 days ago
      • Reported - view

       Hello!

      Thank you for your answer, and sorry for not replying. (

      I could test your formula only today, and it gives me an error:

      1. Function is not defined: numbers([choice]) at line ...
2. Function is not defined: contains (void,number) at line...

      When i change the funtion to number instead of numbers it gives me this error:

      1. Function is not defined: contains (number,number) at line...

      No idea how to overcome this (((

      • Fred
      • 3 days ago
      • Reported - view

       Oops, you said multiple treatments. Try:

         if contains(Treatment.Product.number(Type),5) then
      Paid * 0.25
   else
      Paid * 0.15
   end
      • Agassi
      • 21 hrs ago
      • Reported - view

       That worked, thas a lot Fred!

      Can i ask you how did you figure it out that it should be 

      contains(Treatment.Product.number(Type),5)

      and not

      contains(numbers(Treatment.Product.Type),5)

      ?

      I mean, i could not find any explanation in function description or elsewhere... Its just experience, or there some more learning materials somewhere?

      Best regards,

      Agassi

      • Fred
      • 14 hrs ago
      • Reported - view

       Just from experience. But using troubleshooting steps.

      When I did something similar in my test DB, it didn't work. So the first thing I did was create another formula field and put my version of:

      numbers(Treatment.Product.Type)

      I got an empty formula field. So that tells me that Ninox is not returning a value that it can work with.

      First, numbers() only works for multi-choice so that had change. But number() didn't work either.

      After thinking some more and it made sense that Type is the field that is the choice field, we need wrap number() around that specific field.

      Another helpful field is debugValueInfo(). If you wrap your results with that then you will see what kind of data (string, number, any, etc) and see if the data is put into an array or not.

      Anyways, also writing lots of code.

Login to reply

Content aside

  • 14 hrs agoLast active
  • 5Replies
  • 47Views
  • 2 Following