4 Replies Latest reply on Aug 20, 2010 7:08 AM by michael_1

    Exclude field



      Exclude field


      Hi all first post for me. I am using FM to get records via a relationship to a number field. It works well with numbers but if the entry is numbers and letters it then chooses the number and displays this.

      e.g If I put in 121 it gets the records for 121 fine but if I enter ad121c it then gets 121 records. How do I exclude those entries with a combination of text as well as numbers.

        • 1. Re: Exclude field

          Make the two match fields in your relationship fileds of type text instead of number.

          • 2. Re: Exclude field

            Thanks that works as you suggested but there is complication which I didn't mention. Later this field is used to order so it has to be in number format as ordering numbers listed as text doesn't work to well. I realise that all fields should be text unless you want numbers for a reason e.g. calculations or ordering.

            FM is new to me as all my database work has been in Access so I am on a steep leaning curve as FM is somewhat different.

            • 3. Re: Exclude field

              You can add a calculation field for your number format.

              Simply put the name of this field in the specify calculation box and specify Number as the return type. Use that for specifying your sort order. (You don't have to place this field on your layout anywhere, just update your sorts to refer to it instead of the original, now text field.)

              From a design stand point, you may want to modify things so that the non-numeric text is in one field and the number is in another. A Calculation field or just two layout merge fields side by side can be used to display the numbers in text in combination.

              Even in Access, I'd keep the numbers and text in separate fields.

              • 4. Re: Exclude field

                Thanks for that just what I wanted.  Don't go away I will almost certainly be back for more.