8 Replies Latest reply on Sep 9, 2011 11:04 AM by willrollo

    Case/If when constantenating fields


      Case/If when constantenating fields



      I have 5 fields in my 'Enquiry' Table. 

      Title (drop down list of 8 or so different titles)

      First Name

      Last Name.


      Addressed to:


      What I want is for an automated calculation to appear in the "addressed to:' field that is determined in the other fields. 

      If a company name has beenentered, then I want the company name to appear in the 'addressed to:' field. If not, then I want the "title First name and last name' to appear. However, there is a list of othet titles that are constructed differently. E.g if Mr is selected from 'title', then I would like only the first name and last name PLUS "esq" to appear in the 'addressed to:' field. Like wise, if "Lady' is selected, I want to the The Lady 'Last name' to appear. 

      I have had some success with this but only with a couple of choices as am getting confused with the brackets, Ifs. else ifs etc...!

      Would appreciate a helping hand!

      Also, What sort of quick script could I write on 'exit layout' to ensure that these fields have been completed? IS there anyway of making the required, incomplete fields go red por are asterisked like on a web form?

      Thank you 



        • 1. Re: Case/If when constantenating fields

          I never use an IF statement, only ever a Case.  If the other functions of the Case aren't needed then it is exactly the same number of keystrokes, and if at some later stage I want to add more criteria then they are easier to add to a Case Statement.  And when they need to be nested I find a Case Statement much easier to follow.  There probably is a scenario where an If Statement is essential, but I can't remember meeting one yet.

          You can progressively work through the series of choices you outlined in the posting.  Just make sure that they work sequentially - as soon as the Case Statement meets a match it will stop.


          For example:

          Case (

          CompanyName > 0 ; CompanyName ;

          Title = "Mr" ; FirstName & " " & LastName & " esq" ;

          Title = "Lady" ; "The Lady" & " " & LastName ;

          [more criteria in here] ;

          FirstName & " " & LastName





          • 2. Re: Case/If when constantenating fields

            Perfect - thanks

            Any ida on the second question?Undecided

            • 3. Re: Case/If when constantenating fields

              Conditional formatting can change the fill color of empty fields. the IsEmpty ( self ) expression can be used for that. You can also include a reference to a global variable so that only fields that are empty and this globla field is set so that the fields show red only after they try to exit the layout.

              OnLayoutExit can run such a script.

              If [ Not IsEmpty ( table::field1 & table::field2 & table::field3 // and so forth) ]

              can check for empty fields.

              • 4. Re: Case/If when constantenating fields

                That's great - how do I get the conditional formatting to work once the If function script has completed?

                See the attachment for my script so far!

                Thsnk you

                • 5. Re: Case/If when constantenating fields

                  If [ Not IsEmpty (.....
                     Set Variable [$$ErrorFlag ; Value: True ]
                     Show Custom Dialog [.....


                  Your conditional format would be:

                  IsEmpty ( Self ) and $$ErrorFlag


                  You'd also need to use the onRecordLoad trigger to set the $$ErrorFlag variable to False so that you always start with the flag variable not set when you first create a new record.

                  • 6. Re: Case/If when constantenating fields

                    Hi Phil

                    Thank you for that.

                    One more thing, I mentioned in aprevious post thaI have a drop down menu on mynew enquiry layout - here is an overview again..

                    In my 'Enquiry' table, i have a field with drop down menu (Order type) with choices to "Make order", "make quote"etc. On clicking one of these, the user is taken to either the quote or order layout and the customer information is passed through. I used a script trigger on object exit to change the layout if "Make Order" is detected inthe order type field or the quote version. It has a custom dialogue asking if the user wants ot do this. 

                    All this works fine. However, the drop down menu seems to have the last option chosen, on display before being clicked on. At thagt point it shows the avaianle options and it is fine. Is there anyway of hiding this info and having a blank field unti the user clicks into it? Or having my own text over it that disappears once clicked on, EG Sales. I have tried putting field to back, covering it up, creating a blank line on the value list, but nothing works. Any ideas?

                    • 7. Re: Case/If when constantenating fields

                      Set Field [YourTable::YourDropdownlistField ; "" ]

                      Will clear the field.

                      • 8. Re: Case/If when constantenating fields

                        Thanks - Always seems so simple once you know how!