7 Replies Latest reply on Dec 27, 2014 2:04 PM by philmodjunk

    Validating a list of numbers



      Validating a list of numbers


      Hello all,

      I am trying to validate a list of numbers, but there are specific parameters that I want approved. The list needs to be validated so that it only allows numbers in counting order (i.e. 1,2,2.5,3,3.1,4,6,etc.) as that list shows it needs to allow for decimal usage. It also needs to allow repeat numbers. If it can validate all those things, that would be useful. I thought I could manage it simply with a validation by calculation of:

      number  < GetNthRecord ( number ; Get ( RecordNumber ) + 1 ) 

      (with number being the field validated) But this evaluates false at incorrect times. I'm unsure what to use to get this working. Please let me know if you have any insight to this particular situation.

      Thanks so much

        • 1. Re: Validating a list of numbers

          Make sure that your field is defined to be of type number, not text and that any calculation fields have Number specified as a result type.

          And if each of these numbers are in a different record as appears to be the case, they can be sorted into a different order or unsorted quite easily and this will affect your validation and in fact, given how easy it is to sort the values correctly into this order, I'm not sure that I understand the user scenario where you'd be needing this validation to take place.

          • 2. Re: Validating a list of numbers

            Thanks for the quick reply!

            I can explain a little further what I am doing.

            I have a list of cues based off of a script. It uses the Page number to automatically assign a Scene number from a different table that gives Scene numbers and start pages and end pages. It should always sort by Scene number > Page number > Cue number > Part number. The part I'm having trouble with is to always make sure the Cue numbers go in sequential order (on the same page) while allowing for duplicates that are the same Cue number with different Part numbers. I believe this might be easier to do with a script then validation, so I might try that route. Another option I could go with is conditional formatting that highlights when Cue numbers are out of order.

            Thanks for your help.

            • 3. Re: Validating a list of numbers

              That helps, but there's just not enough detail in your posts to be able to suggest anything specific. How your data is structured into fields, tables, etc is a detail we'd need to be able to understand.

              • 4. Re: Validating a list of numbers

                So let's say I wan to go on the conditional formatting route. I have attached a screenshot of the Cue list table, it should give you the general idea of the structure. Also You can see that it has some Q #s that don't quite match their placement. I have also created an invisible error field that I would like to base the conditional formatting off of. So I wrote a script as follows:


                If [ GetField ( Cues::Q # ) > GetNthRecord ( Cues::Q # ; Get ( RecordNumber ) + 1 ) ]
                Set Field [ Cues::Error Field ; "Q#" ]
                Set Field [ Cues::Error Field ; "" ]
                End If


                I thought this wold set the "Error Field" to "Q#" if the current Q # was larger than the next one, Thus letting me know when I have a Q # that doesn't quite fit. But it is evaluating all fields except the last field as true in the if statement. If this is something simple I apologize, and thank you for all your time and thought with helping me.

                • 5. Re: Validating a list of numbers

                  Remove the getField function. It doesn't work like you are expecting it to and you don't need any such function anyway.

                  If [ Cues::Q # > GetNthRecord ( Cues::Q # ; Get ( RecordNumber ) + 1 ) ]

                  But wouldn't it be simpler to sort the records the way you need them and then use Replace Field Contents with its serial number option to renumber them to show the desired order accurately?

                  • 6. Re: Validating a list of numbers

                    Thanks that helped! Not sure why I was using that anyways.

                    The trouble is that things get added in once a cue is made it can't be renumbered. Hence the decimal Q #s. sorting by Q # would make this issue visible, but it doesn't allow me to use the sub-summary by Scene # to make scene headings as I would like to. But now the conditional formatting is working right with that script except for the last record, is there a way to prevent it from coming up as incorrect as there is no record after it?

                    Thanks so much!

                    • 7. Re: Validating a list of numbers

                      If [

                      Let ( R = Get ( RecordNumber ) ;
                              If ( R = Get ( FoundCount ) ; False ; // This is the last record. No error.
                                   Cues::Q # > GetNthRecord ( Cues::Q # ; Get ( RecordNumber ) + 1 )
                                  ) // If
                              ) // Let]