0

Using triggers for yes/no field

I have tried using the visual part of the Ninox formula and various forms of this text in the script writing, all to no avail. 

if Status = ("Closed" or "Expired") and 'Campaign Premium' > 0 then
    Winner = "True"
else
    Winner = "null"
end

I've tried that code in the "Winner" field's Display field only  if (which hides the field at all times if I leave it there), and I've tried it in the After Update which allows display of the field, but under all conditions and does not ever set the checkbox to Yes.

Any help is welcomed!! Thank you.

 

Here's the pertinent database layout. 

  • I have a field named "Status" that has only 5 options, 2 of which are Closed and Expired
  • I have a currency field named "Campaign Premium"
  • I have a yes/no field named “Winner”
  1. I want the Winner field to ONLY display IF the Status field is either Closed or Expired.
  2. When displayed, I want it set to “Yes” if the Campaign Premium is greater than Zero

18 replies

null
    • Fred
    • 4 mths ago
    • Reported - view

    Try:

    if Status = "Closed" or Status = "Expired"

    Ninox doesn't have a way, that I know of, to combine search terms.

      • Rick_Castellini
      • 4 mths ago
      • Reported - view

       Thanks for the reply! I gave it a try, but no love. 😑 Here's my code:

      if Status = "Closed" or Status = "Expired" and 'Campaign Premium' > 0 then
    Winner = "Yes"
else
    Winner = "No"
end
      • Fred
      • 4 mths ago
      • Reported - view

       

      if Status = "Closed" or Status = "Expired" and 'Campaign Premium' > 0 then
    Winner := "Yes"
else
    Winner := "No"
end

      You need colons in front of the equal if you want to set the field Winner to Yes or No. You can also do 1 or 0 or true or false (with no quotes even).

      Your display only if command would be:

      Status = "Closed" or Status = "Expired" and 'Campaign Premium' > 0

      It is inferred to be an if statement so you can leave it out.

      • Rick_Castellini
      • 4 mths ago
      • Reported - view

       Wow...again, really appreciate the help. I'm totally new to Ninox, but love its possibilities. 

      I tried again...if I put anything in the display field only box, it hides the field all the time. Here are a couple of screenshots. The formula still isn't "flipping the switch" on the field

    • John_Halls
    • 4 mths ago
    • Reported - view

    Hi Rick

    You need to be checking for an update in the Status and Campaign Premium fields but you have your code in Winner. Place the same code in these two fields and take it away from Winner and all should be good.

    Regards John

    • John_Halls
    • 4 mths ago
    • Reported - view

    You mght also need to change Status = "Closed" to text(status) = "Closed" or better still status = 1 (the option number of status for Closed). And do the same for Expired.

    Ninox is encouraging us away from hard code in our scripts by working this way, It allows for a change in the name of option 1 without breaking the script

    • Mel_Charles
    • 4 mths ago
    • Reported - view

    Rick - New to Ninox! Then welcome to user forum. You will find lots of help and support from some very dedicated users.

    Johns right, Better to go with the index number of the choice. You will see the index numbers in edit mode on the choice itself.

    Once you are fully immersed in Ninox you will love the way it works. Not perfect, but very satisfying to use.

    • Rick_Castellini
    • 4 mths ago
    • Reported - view

    Thanks Mel and John and Fred. 

    I was able to get the get my winner field to show only when I want it by adding this code to the display only if section:

    Status = 2 or Status = 4

    Appreciate the help. 

    I tried adding the other code to the Campaign Premium field, but get the following error:

      • Fred
      • 4 mths ago
      • Reported - view

      Formula fields can not modify other fields. You will need to find a regular text/number/choice (there are probably a few more) field that you add to Trigger after update, or you can add it to the Trigger after update of the whole table.

      • Rick_Castellini
      • 4 mths ago
      • Reported - view

       Yes, it didn't look possible, but why don't you think it's working in the Winner field itself?? No errors, just sits there.

      • Fred
      • 4 mths ago
      • Reported - view

      to add multiple commands you need to end the previous command with a semi-colon. Ninox processes the commands starting with line 1 and going down. So you need to make sure your final line is what you want to see. In your example you have a calculation field, if you wanted to run other code you need to make sure to add it before the calculation field, other wise the calculation field results won't show up.

      • Fred
      • 4 mths ago
      • Reported - view
       said:
      Yes, it didn't look possible, but why don't you think it's working in the Winner field itself?? No errors, just sits there.

      I'm not sure what you mean by "just sits there".

      To me it doesn't make sense to put a Trigger after update in Winner that then modifies Winner based on another field result. I created something similar and it feels weird that when I make a selection in the field it changes. It makes it seem like something is wrong with the DB.

      Seems like you would find a work flow that once you update a field it would be natural for it to modify the Winner field.

      • Rick_Castellini
      • 4 mths ago
      • Reported - view

       Thanks for semi-colon tip!

      I'm mostly functional now by adding my trigger code to the table AND, as mentioned, I've got the hide field working properly too.

      HOWEVER, why doesn't Ninox update ALL records that apply to the code? It only flips my Winner switch when I'm in a particular record. So, I'm ok for all new records, but none of my old records unless I go back and change something in each one. Solutions?

      I wouldn't even need this switch if Ninox had a query language, but creating reports and querys is beyond me.

    • Fred
    • 4 mths ago
    • Reported - view
     said:
    HOWEVER, why doesn't Ninox update ALL records that apply to the code? It only flips my Winner switch when I'm in a particular record. So, I'm ok for all new records, but none of my old records unless I go back and change something in each one. Solutions?

    The code is not setup to update a group of records. You didn't say you wanted that so that is not what was written. Ninox is doing exactly what is written. No more. No less.

     said:
    I wouldn't even need this switch if Ninox had a query language, but creating reports and querys is beyond me.

    Currently in any DB you will have to learn some coding language to do querys. Natural language coding a bit further off in the future.

    If you are willing to learn, we are here to guide.

    • Rick_Castellini
    • 4 mths ago
    • Reported - view

    Really appreciate it Fred. I'm definitely willing to learn. My brain is still stuck in the 90s using Microsoft Access...which I understood very well. Ninox has been great and I converted a system I was using Google Sheets for to Ninox in the spring and have been very happy. I just KNOW that with some knowledge and skill, I could get more data OUT of the database in the form of charts and reports if I can master it. 

    Thanks again!!

      • Fred
      • 4 mths ago
      • Reported - view

      if you learned access then you can pick up Ninox. Like learning a third language. It gets easier the more you know. 

    • Mel_Charles
    • 4 mths ago
    • Reported - view

    Rick

    I too came from the MS Access and Dataease era and I promise that you will be able to do 99% of the stuff you can do with these. Hang in there it definitely will get better.

    Just a couple of tips Unlike access/dataease you do specify a key field relationship.- You specify a table relationship once do all fields are linked. Keep in mind that you are not designing a table structure and then the form afterwards, it's all wrapped into a one step process.

    There are some excellent videos on utube etc, look for Nioxus or here
    https://www.youtube.com/@NinoxLearning/videos

    If you have a copy of the MS Access Bible (you know the one - that huge tome with like 900+ pages). Then either throw it away or use it as a door stop - you won't need it anymore cos Ninox is totally different. 

    So stuff like VLookup is consigned to the bin!  Mind you, I still love Excel with VBA

      • Rick_Castellini
      • 4 mths ago
      • Reported - view

       Thanks. I guess I'm finding it difficult to extract data and reports from the data I'm entering. I have my forms and data entry pretty much nailed down now, but would like to learn how to "mine the data" that's their now through reports and the dashboard. I've watched the videos and will continue to look through them again. 

Login to reply

Content aside

  • Status Answered
  • 4 mths agoLast active
  • 18Replies
  • 107Views
  • 4 Following