1 2 Previous Next 17 Replies Latest reply on May 4, 2012 12:04 PM by philmodjunk

    insert new records in another table

    rounakjain

      Title

      insert new records in another table

      Post

      I am working on creating an Invoice database. Here's my attempt:

      http://dl.dropbox.com/u/9738427/GroundUp%20copy.fmp12.zip

       

      I enter data in Invoice Table.

      Through a Join Table, I retrieve Customer wise prices for distinct products.

      Currently, it works like this:

      1. I create a new customer in the "Customer" table. I enter some products and their prices in the "Customer Table" through a portal of Join Table.

      2. Then I go to "Invoices" table. I enter the customer ID. Lookup finds the Customer Name for this ID. I enter Item Names in "Line Items" table. Lookup retrieves prices for that Item Name for the Current Customer through the Join table. A script "Update Price" executes if I change the "retrieved price" and the new price is placed in the Join Table.

      Now, I want to insert new records in Join Table if I enter a new record (I mean item names which do not already exist in Join table) in the Line Item table. The new item nam, eassociated price of the new item and the current customer ID must be part of that record.

       

      Thanks.

        • 1. Re: insert new records in another table
          philmodjunk

          I wouldn't use Item Name for relationships like you have. I'd use the ProductID instead. A drop down list can list ID's and Item names, but enter the Product ID instead of the name. (This allows you to edit an Item Name in the Products table without the change affecting links to other tables.)

          If I understand what you want. You want to create new records in the join table when adding a record in the line items table for a product that is not yet listed in that customer's join table.

          The OnObjectSave trigger on the drop down list in the LineItems portal can create new records in the join table via a new relationship.

          Create a new occurrence of Join Table and link it to the Line Items table occurrence. Link them by ProductID and CustomerID. Enable the "Allow creation of records vis this relationship" for the new occurrence of the Join Table.

          Now this script step can be used to create new records in the Join Table if the related record does not already exist:

          Set Field [Join Table 2::Product ID ; LineItems::Item Name ]

          • 2. Re: insert new records in another table
            rounakjain

            Thanks for your reply. I have spent some hours trying different things to come up with what I want. I will accomodate the changes you have suggested. But for now, I would like to know if you could just fix a small problem.

            If I understand what you want. You want to create new records in the join table when adding a record in the line items table for a product that is not yet listed in that customer's join table.

            Correct. I have been able to achieve it except for one minor trouble. For every new product that i enter, i have to click a button to execute the "Refresh List" script. Please write me a script with an "IF" statement for the portal field - item name to check if the product already exists----if not run "Refresh List". You can see my file here:

            http://dl.dropbox.com/u/9738427/GroundUp%20copy%202.fmp12.zip

            • 3. Re: insert new records in another table
              philmodjunk

              The script I suggested does not require using an If step. It will update the table automatically each time you enter a new record in LineItems. If a realted record already exists, no change to your data occurs as this steps sets a field to data that it already contains.

              I don't see what "refresh list", which just sorts the records, has to do with this process. What does that do for you here?

              • 4. Re: insert new records in another table
                rounakjain

                Here's my file: http://dl.dropbox.com/u/9738427/GroundUp%20copy%203.fmp12.zip

                I have accomodated the suggestion you made regarding use of Product ID instead of Product Name.

                If I enter a new record in the Invoice table (same as saying "If i create a new invoice"), I do this:

                1. I press enter to accept the auto-generated Invoice serial number.

                2. I enter the date

                3. I skip the Customer ID field (it is only for test purposes)

                4. I enter the Customer name. At this point, the Customer ID field finds itself the appropriate Customer ID if the Customer already exists. If not, then it creates a new Customer ID.

                5. I want to repeat the behaviour in step 4 with Products entered in Line Table portal present in the Invoice table. I have been able to get the appropriate ID if the Product already exists. The problem is no new Product ID is generated if I enter a new Product.

                • 5. Re: insert new records in another table
                  philmodjunk

                  Take a look at the value list definition in your line items portal for ProductID. It should be based on values from the products table. When I checked this earlier, you had it set up to list items from the line items table.

                  Please confirm that this is what you are trying to do:

                  You have started a new invoice.

                  You then select a product in the lineitems portal from the list of those in the Products table.

                  If this product is not already listed in the join table for that customer, add it to their join table.

                  What I am trying to be sure about is that "new product" means a new product for this customer, not a new product to your entire database.

                  • 6. Re: insert new records in another table
                    rounakjain

                    What I am trying to be sure about is that "new product" means a new product for this customer, not a new product to your entire database.

                     

                    "new product" means a new product for the entire database.

                    • 7. Re: insert new records in another table
                      philmodjunk

                      Then you need to create a new record in Products, not the join table. Your original request was to create new records in the Join Table--which is a different situation than you are now describing.

                      You'd need a button for performing a script for this. The following script creates a new product record in the products table and then enters its ID into a new record in the lineItems table:

                      Freeze Window
                      Set variable [$InvoiceNo ; value: Invoice::InvoiceNo ]
                      Go to Layout ["Product" (Product)]
                      New Record/Request
                      Set Variable [$ProductID ; Product::ProductID ]
                      Go to Layout ["line items" (Line Items) ]
                      New Record/Request
                      Set Field [Line Items::Invoice No ; $InvoiceNo]
                      Set Field [Line Items::ProductID ; $ProductID]
                      Go to Layout [original layout]

                      Note, this will not work unless you change the relationship from Line Items to Product to match by Product ID instead of Product Name. It also enter no information into the Product table. If you have fields from the Product Table that are in your line items portal, you can enter that new data there (such as an Item Name). You could also put a pause/resume step just before Go to Layou ["line items"] to pause the script on the Product layout where you can enter the additional data before continuing the script.

                      • 8. Re: insert new records in another table
                        rounakjain

                        If you have fields from the Product Table that are in your line items portal, you can enter that new data there (such as an Item Name).

                        I cannot enter data into such field unless i enter the Product ID first. Thats understandable for new Product names (the script will first create the id and then i will enter the product name in such field.) But not appropriate for existing item. I would want to enter the name of existing item right away not its Product ID.

                        • 9. Re: insert new records in another table
                          philmodjunk

                          Which is why you would run the script to create the new product record, create a matching line items record and THEN you can edit the other product fields.

                          • 10. Re: insert new records in another table
                            rounakjain

                            Kindly see me previous edited reply

                            • 11. Re: insert new records in another table
                              philmodjunk

                              You still should use an ID number based relationship. Your value list can list the ID number as field 1 and the product name as field 2. That way you can select the product by name, but the value list enters the ID number.

                              • 12. Re: insert new records in another table
                                rounakjain

                                Here's my new file:

                                http://dl.dropbox.com/u/9738427/Restart%20copy.fmp12.zip

                                Kindly look at my Invoice Table.

                                Is this how I am supposed to enter data by ID Name? Is there a better way to avoid seeing the Product Nos and instead see only the Product Names once I enter the choice from the drop-down list?

                                • 13. Re: insert new records in another table
                                  philmodjunk

                                  I don't see a need for the script trigger you have on this field, but that's a different issue.

                                  What you have in place is the simplest approach for doing this. It is not always the best approach.There are a number of alternatives you can set up.

                                  What issues does this approach cause for you? (I can guess, but would like to be sure..)

                                  • 14. Re: insert new records in another table
                                    rounakjain

                                    I don't see a need for the script trigger you have on this field, but that's a different issue.

                                    Then there must be something fundamentally wrong with what I am doing. Because as soon as I remove the script trigger and exit from the LTProduct Rate, I see the error:

                                    This field cannot be modified until “LTCustomer ID” is given a valid value.

                                    (Try entering more than one record in the line table if you cannot see the error)

                                    What issues does this approach cause for you? (I can guess, but would like to be sure..)

                                    I have two fields when the end user needs only one. The end user needs to enter the product name and needs to see the same..nothing less..nothing more. The end user does not need to see the ID.

                                    1 2 Previous Next