6 Replies Latest reply on Nov 11, 2008 9:01 AM by DrDave

    Writing Record Data

    DrDave

      Title

      Writing Record Data

      Post

      I'm new to FM (Pro) and someone sent me a database that I'm working with. Here is the situation.

       

      I havew a customer database and a purchase database.

       

      When I add data, I use a layout that contains information from the customer database and (I think it's called a portal) information from the purchase database. In fact, I can add up to four purchases in this layout.

       

      One more piece of info. When a purchase is added, there is a purchase number that is the customer number plus some text (like 1234INT).

       

      Instead of entering this purchase number for every purchase, I specified a validation function that simply says, add the text to the customer number and store it in the purchase number field. It works great...under some conditions.

       

      Here is my problem.

       

      When I add a record, I enter the customer number, but the validation on the purchase number does not work on the purchase database unless I "go to the next customer record, and then return to the current customer."

       

      I think this forward-back action writes the data to the record and then the validation function can see it.

       

      Is there something that I can do to write the customer record data as soon as I enter the customer number.

       

      Thanks for the help...and if you actually read this WHOLE message, you are my hero! :-) Sorry for the long message. 

        • 1. Re: Writing Record Data
          TSGal

          DrDave:

           

          Thank you for your post.

           

          When you enter a purchase number in the Customer table and skip to the next field, FileMaker should automatically display all records from the purchase table that matches that purchase number.

           

          Since your key field is a calculation, what is the formula of your calculation?

           

          Here are the steps I took:

           

          1. I created a table "Primary" with three fields:

          First Half (Text)

          Second Half (Text)

          CalcKey (Calculation) = If (IsEmpty (First Half) or IsEmpty (Second Half), "", First Half & Second Half)

           

          2. I created a second table "Secondary" with three fields:

          Key (Text)

          Field1 (Text)

          Field2 (Text)

           

          3. I entered the following four records into "Secondary"

          (Key - Field1 - Field2)

          A1 - First A - More First A

          A2 - Second A - More Second A

          B1 - First B - More First B

          B2 - Second B - More Second B

           

          4. I created a relationship between "Primary" and "Secondary" table using "CalcKey" = "Key"

           

          5. I switched back to "Primary" and created a portal into "Secondary" and showing all fields.

           

          6. In Browse, I create a new record and enter information into First Half and Second Half.  For each value, the information appears in "Secondary"

           

          If I enter a new values:

          First Half = "C"

          Second Half = "1"

           

          ... CalcKey = "C1", and no record appears in "Secondary".  If I enter information into the portal, the value "C1" will then be added to the "Key" field in "Secondary".

           

          Let me know if I missed something, or let me have your example, and I will try to duplicate it here.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Writing Record Data
            Vaughan
              

            "Instead of entering this purchase number for every purchase, I specified a validation function that simply says, add the text to the customer number and store it in the purchase number field. It works great...under some conditions."

             

             

            An auto-enter calculation should be used for this, not a validation calculation. Validation is used to check whether the field entry is *correct* or within a range. Auto-enters are used to automagically type things into fields.

             

            The auto-enter calculation could be:

             

            PurchaseNumber & "INT"

             

            ... if the text never changes, or to make the text the first 3 characters of the person's last name use:

             

            PurchaseNumber & left( LastName ; 3 )

             

            These calculations assume there are fields named "PurchaseNumber" and "LastName".


             

            When setting up the auto-enter calculation, it can be made to calculate only if the field is empty, or every time the fields in the calculation change. You need to decide whether the purchase number should change once it's been entered or stay the same, even if the person's name is changed.

             

            I'd not recommend using this field as the primary key for a relationship. Instead use an auto-entered serial number. 

            • 3. Re: Writing Record Data
              DrDave
                

              Thank you TSGal. It's going to take me some time to work through what you have suggested here (because I'm a newbie). However, I did want to thank you for giving this suggestion.

               

              Thank you...stay tuned. 

              • 4. Re: Writing Record Data
                DrDave
                  

                Hi Vaughan,

                 

                Because I'm new to FM, I'm afraid that I used a term incorrectly and your main suggestion is based on my misleading info -- sorry about that.

                 

                More specifically, I AM using the auto-enter calculation and NOT the validation, as I initially indicated.

                 

                My problem however is that if I -- for example -- edit an existing record, and while doing so, enter data into the CustomerNumber field, the auto-enter function does not work in my portal, unless I "force a save" after I enter the CustomerNumber. When I say, "force a save," it simply means that I go-forward in the database, and then back to the record.

                 

                I tried this, but it did not work. I wrote a script called dhFlushCache and it simply had a "flush cache to disk." I would then edit a record by entering the CustomerNumber, and then run the script (which I hung off Cmd-0). As I said, I THOUGHT this might work, but it did not.

                 

                Any ideas?

                 

                • 5. Re: Writing Record Data
                  TSGal

                  DrDave:

                   

                  Thank you for the clarification.

                   

                  Yes, if you enter information into the portal, the data isn't committed until you exit the record.  However, if you have the option to "Allow creation of records in this table via the relationship" turned on, then you should be able to tab to the next record in the portal, and the data should be updated.  This option is under File -> Manage -> Database.  Click on the Relationship tab, and then click on the icon that connects the two tables together.  You'll see the option on both sides for each table.

                   

                  Let me know if this helps. 

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Writing Record Data
                    DrDave
                      

                    Bingo!

                     

                    Thank you for this help. I'm starting a 3-day FM course on Monday and really looking forward to it.

                     

                    Take care.

                     

                    Dr Dave