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

    Validation Calculation

    lkn4atlantis

      Title

      Validation Calculation

      Post

      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
          philmodjunk
            

          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
            lkn4atlantis
              

            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.

            Thanks

             

             

             

             

            • 3. Re: Validation Calculation
              philmodjunk
                

               

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

               

              Try

              # 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
                lkn4atlantis
                  

                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
                  philmodjunk
                    

                  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
                    lkn4atlantis
                       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
                      davidhead
                        

                      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
                        philmodjunk
                           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
                          lkn4atlantis
                            

                          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.

                          Thanks

                          Amy

                          • 10. Re: Validation Calculation
                            philmodjunk
                              

                            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
                              lkn4atlantis
                                

                              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.

                              Amy

                              • 12. Re: Validation Calculation
                                philmodjunk
                                   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
                                  lkn4atlantis
                                     Ok thank you again for all your help!!
                                  • 14. Re: Validation Calculation
                                    davidhead
                                      

                                    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