5 Replies Latest reply on Jul 1, 2009 9:55 PM by timothy2k

    Scripting a new record in a related table, keeps overwriting original?

    timothy2k

      Title

      Scripting a new record in a related table, keeps overwriting original?

      Post

      Hello FellowFilemaker's,

       

      Was wondering if anyone sees the (probably simple) mistake in my script logic below? I am trying to have a button on my "Estimate" layout create a new record in a table "Quote" based on two fields in the current Estimate layout/table.

       

      When I create a new estimate, is it assigned an Estimate ID. I want vendors to bid on that estimate, by creating a quote to send them. So I have 1 field called quote_vendor_lookup(to enter Vendor code) and 1 button (to create the related quote) on the estimate record that allows a user to create a quote after entering the Vendor code. The quote should be unique for each esimtate_id + vendor code combination. I have this working, but only for the first time i use the button. Once I go back and try to enter a new quote for a different vendor (based on the same estimate) it simply overwrites the record I just created. This is strange because I am saying "new record" in the script below, or at least I think I am. IS it possible the new record does not mean new record in the quote table? (there is no way to specify an outside table for new record, that I see).

       

      I have looked through all the script options but must be missing something, as the Estimate/Vendor Code combination shuld be unique and create a new record, not overwrite the old one.

       

      Here is my "Create New quote" script for the button on the Estimate Form:

       

      Create New Quote
      Go to Layout [ “Quote” (Quote) ]
      New Record/Request
      Set Field [ Quote::vendor_code; Quote::vendor_code = Estimate::quote_vendor_lookup ]
      Set Field [ Quote::quote_id; Quote::quote_id = Estimate::estimate_id ]
      Commit Records/Requests
      [ Skip data entry validation; No dialog ]

       

      As I said, this only works the first time, then it just overwrites the first record the second time. Maybe this has to do with my actual database table fields and not the script?

       

      Any pointers would be great.

       



        • 1. Re: Scripting a new record in a related table, keeps overwriting original?
          mrvodka
             not 100% following your business logic here but you should capture the field values into variables first prior to the new records step. Then set the fields in the new record with those variables.
          • 2. Re: Scripting a new record in a related table, keeps overwriting original?
            timothy2k
              

            I "think" the data I need is captured before creating the record, but maybe it's not. 

             

            The quote should be created based on two fields in Estimate. 1- estimate_id and 2-vendor_code

             

            I believe those are both present when I click the "Create new Quote" button. 

             

            I'll include a better description of the business logic below, in case it helps:

            -------------------

            Each estimate has an assigned estimate_id.  For example sake, let's use use Estimate 10019. I want my button to create  to create quote requests for this estimate each time I use it, for different vendors (for example we will use two vendors-let's say vendor code "abc" and "def"). 

             

            There is a field on the estimate form that says "Enter Vendor ID" followed by the button to create a quote. After entering vendor code "abc" I want the button to create a new quote with quote_id = 10019 and the vendor code = abc.  That is the only quote I can create for that combination.

             

            Now I go back to estimate 10019, and want to create a quote request for vendor "def".  I enter "def" in the "Enter Vendor ID" field, and click the button to create new quote.  This should create a NEW quote with quote_id=10019 and vendor code = def.

             

            In the quote table, I use a field that concatenates quote_id and vendor code to store a unique value (such as 10019abc), that way only one quote can be created per estimate/vendor.

             

            Hope this helps.

             

             

            • 3. Re: Scripting a new record in a related table, keeps overwriting original?
              timothy2k
                

              One thing I have noticed using the "data watcher" while running my script:

               

              The values for each field I am trying to set in the quote table keep getting set to "1"

               

              Even though I am specifiying they be set to current fields in the estimate form.

               

               

              My script uses:

              Set Field[Quote::vendor_code; Quote:vendor_code=Estimate::quote_vendor_lookup]

              Set Field[Quote::quote_id; Quote::quote_id=Estimate::estimate_id]

               

              It seems to be ignoring the values that are in those fields and setting them to 1 in the quote table.  Possible bug here??

               

               

              • 4. Re: Scripting a new record in a related table, keeps overwriting original?
                comment_1
                  

                I am afraid you have two false assumptions here:

                 

                 

                1. The formula:

                 

                Quote:vendor_code=Estimate::quote_vendor_lookup

                 

                returns either true (1) if the two sides of the equation are the same or false (0) otherwise. If you want to set Field X to value y, use:

                 

                Set Field [ Field X ; y ]

                 

                not 

                 

                Set Field [ Field X ; Field X = y ]

                 

                 

                2. When you create a new quote record, it has no related records in another table until the matchfield is given some value (as you are trying to do). Therefore the expression:

                 

                Estimate::estimate_id

                 

                has no meaning at the time you are trying to use it - see what mr_vodka said earlier.

                 

                • 5. Re: Scripting a new record in a related table, keeps overwriting original?
                  timothy2k
                    

                  I had not see the variables option before, but it worked like a charm.  Thanks for the info! Gave you kudos and a solution credit.