1 2 Previous Next 15 Replies Latest reply on May 14, 2009 12:26 PM by lkn4atlantis

    Validation Calculation



      Validation Calculation


      I'm sure this is an extremely basic question to most of you, but i am extremely new to filemaker (I'm creating my first file). Anyway, what I want to do is have a validation in one of the fields  so when you type a number in it, it has to be equal to or less than another field.  In other words, I have a list of a card collection that I am selling. I have a column for the name, type, # of cards, value, ect.  I have another field for when someone wants to buy a card, obviously they can't buy more cards than what I have, ergo, the field must be equal to or less than the number of cards I have. I would appreciate any help.  Thanks.

        • 1. Re: Validation Calculation

          Use a validate by calculation field option:


          Self < NumberField


          Put the name of your other field in place of "NumberField"


          You can then set a custom validation message to tell the user why it's an error.


          You'll also probably want to uncheck the "allow user to override during data entry" option.

          • 2. Re: Validation Calculation

            First off thank you for your reply.

            Second, I'm having trouble understanding what you're saying.

            I go to file - Define fields, Click on the field name I want to set a calculation for (# buying) then options. Validation tab - Validation by calculation. The Specify Calculation box comes up... That's where I get lost.  I tried typing "Self < # of cards (# of cards being the field it must be equal to or less than). When I click Ok   I get an error that states "This field cannot be found".

            What am I doing wrong? Any help is appreciated.






            • 3. Re: Validation Calculation


              I'm using FMP 10, if you are using an older version, you may have to rewrite the expression as:



              # buying < # of cards


              You can select each of the two fields by double-clicking their names in the list of fields you see in the Specify Calculation window.

              • 4. Re: Validation Calculation

                Thank you for your prompt reply :) 

                I am using version 5.

                I tried what you told me and it somewhat works. It won't let me enter an amount equal to or more than. Which is half of what I want.

                Do I need to have the "<" underlined to specifiy equal to or less than? If so, how do I do that?


                • 5. Re: Validation Calculation

                  Thanks for the version info. (I'm still converting 5.5 files to 10 so I'm familiar)


                  Use <= instead of < and it should work for you.

                  • 6. Re: Validation Calculation
                       Thank you very much for your help. Wondering if you could help me with something else? I'm creating another FileMaker Project from an Excel spreadsheet. This project will have approx 50 fields. Is there a way to import the field names from Excel, so I don't have to manually enter each one? Thanks much, Amy.
                    • 7. Re: Validation Calculation

                      Hi Amy

                      presumably the field names are in the first row of the worksheet? If so, you can import the file and create a new table from the worksheet that uses the first row for field names. You should see the option to import into a new table at the bottom of the pop-up menu on the top right of the Import dialog (where you usually see the target table). Also check the option down the bottom of the dialog to not import the first record.

                      • 8. Re: Validation Calculation
                           Given that she's using an older version, she may have to drag the excel file over and "drop" it on the Icon for the filemaker app.
                        • 9. Re: Validation Calculation

                          Yes the field names are in the first row of the Excel worksheet.  So, how do I import a file and creat a new table? Like I said I'm really new to File Maker, so if you have time to give me step by step directions, I would really appreciate it.  Also, PhilModJunk replied to your answer and said because I'm using an older version I many need to drag and drop the icon. ???  Thanks again for your help.  I did order some instructional books to learn file maker but they don't have any information about using Excel with Filemaker.



                          • 10. Re: Validation Calculation

                            Select Import Records from the file menu and see if you get an option for creating a new table. (You may need to read up on this tool in help file.) If you can do that, then you can follow Dave's advice.


                            In the older versions of FMP, you may not have that option. If you use your mouse to drag your excel file over and drop it on the icon for filemaker pro, it should create a brand new database file that contains the data from your excel file. (They added that capability back in when the newest version was FMP 4 if memory serves correct.)


                            You can also launch filemaker and choose File | Open and specify "files of type Excel".

                            • 11. Re: Validation Calculation

                              Sweeet!!! thank you so much for your help!!  Can I trouble you for one more question? LOL   Is there a way to have FM show the field name slanted at say a 45 degree angle, so I can fit more info onto the screen?

                              Thanks again.


                              • 12. Re: Validation Calculation
                                   Only if you made your labels rotated graphic objects in some other application and pasted them onto you're layout, I'm afraid. You can rotate text 90 degrees in the newer versions of Filemaker, but I don't see a way for 45 degree rotation.
                                • 13. Re: Validation Calculation
                                     Ok thank you again for all your help!!
                                  • 14. Re: Validation Calculation

                                    Sorry, I missed your comment that you are using FileMaker Pro 5 - that's from last century man! ;)

                                    I only go as far back as FileMaker Pro 6 with what I have installed on my machine so these instructions should be close enough.

                                    Phil was correct - one way to convert the Excel file to a FileMaker Pro file is to drag it onto the FileMaker Pro icon.

                                    I will provide an alternate method:


                                    1. Open FileMaker Pro 5

                                    2. Choose File > Open…

                                    3. Select the Excel file you want to convert (make sure either 'All Available' or 'Excel' is selected for the file type you are looking for) 

                                    4. Click the Open button

                                    5. In the Specify Excel Data dialog, select the worksheet to import, click OK

                                    6. In the First Row Option dialog, select Field Names for the first row

                                    7. Name and save the converted file


                                    And that's it! You will now have a FileMaker Pro file with fields as per the first row and all other rows as records. 

                                    1 2 Previous Next