14 Replies Latest reply on Mar 5, 2013 10:01 PM by mperley

    Copying Data from One Portal to Another

    mperley

      Title

      Copying Data from One Portal to Another

      Post

            

           We're developing a new quoting system for our company. We have items from suppliers that we will use repeatedly in different quotes. Each item is different and their suppliers may offer one price regardless of quantity or one or more price breaks depending on quantity. Because of this, we have an "Item Price Breaks" table that allows us to add one or more price breaks for each item we sell that can be unique to each item. The main pieces of data captured in those price break records is quantity (e.g., 1-100, 101-200, etc), per (e.g., each, case, etc), and then the price.
            
           We then have a separate quotes table in which we can add additional information based on the type of job. We first select a vendor and then an item from a value list of only those items supplied by that vendor. We'd like to pull the related price breaks into the quote and add some additional fields (e.g., decoration cost, total product cost, as well as a gross profit calculation) to the price break data. For example, if we had an item "mug" and there were three price breaks (1-50, 51-100, and 101+) we'd like for that information to populate lines in a portal (3 to match this item) in the quotes table but have additional fields available for data entry.
            
           Is this type of thing possible? How could we make something like that work?

        • 1. Re: Copying Data from One Portal to Another
          philmodjunk

               With the proper relationships you can enter the primary key of each specified price break field into the table where you list each of these items for a given quote.

               But which do you want?

               An entire set of price breaks specified when you select the vendor?

               Or

               To click a specific price break shown in a portal which then adds that one selected price break to your quote?

          • 2. Re: Copying Data from One Portal to Another
            mperley

                 Hi Phil,

                 I'd prefer to have the entire set of price breaks specified when an item is selected. Here's an extended use case:  Let's say I add a new product "T-shirt" in my Items table. Related to this item are three price break records, seen via a portal on the "T-shirt" record, of 1-100, 101-200, 201+ with prices of 1.50, 1.45, and 1.40, respectively. 

                 Then in my quote table I add a new record, select a vendor and then ultimately select this "T-shirt" product. The layout for this record also contains a portal, and when this "T-shirt" item is selected three line (or records) are created that pull the price break and price fields from that item's original price break records. The contents would just be copied into these new Quote portal records so they could be changed as necessary without overwriting the item's original price break data. The portal on this Quotes record would then nave additional fields for further data entry. If an Item has 2 price breaks, only 2 Quote price breaks are created, if the Item has 5, then 5 would be created in the Quote record's portal. If the Item on a Quote record is changed, the lines change to reflect the newly chosen item.
                  
                 I'm trying to avoid having five static price break fields on the item record (i.e., break1, break2, price1, price2) because I can get this to work that way. Using a related price break table and portal looks more elegant and avoids clutter. Would the ideal workflow above be possible to create in FileMaker?
            • 3. Re: Copying Data from One Portal to Another
              philmodjunk

                   It's all possible, I'm just exploring your design options at this point.

                   Normally, quote, invoice and purchase order systems all use a "line items table" for specifying the details of a given quote, invoice or purchase order record. The only difference is the first table shown in the following set of relationships:

                   Quotes---<LineItems>------Products

                   Quotes::__pkQuoteID = _fkLineItems::_fkQuoteID
                   Products::__pkProductID = _fkLineItems::_fkProductID

                   (in the Filemaker 12 invoices starter solution, they call the "lineitems" table, InvoiceData.)

                   See the first post of this thread for an explanation of my notation: Common Forum Relationship and Field Notations Explained

                   Does this match what you are trying to do?

                   For example, might a single quote list "T-Shirts" and "Sweaters" as part of the same quote? If so, you would need 6 records in line items to show your 3 price breaks for each item.

                   If, on the other hand, your quotes are limited to a single product (Just a quote for "T-shirts" with a separate quote for "Sweaters"), then the details here in how we would make this happen change.

              • 4. Re: Copying Data from One Portal to Another
                mperley

                     What I initially had started working towards is very similiar to what you described in your last sentence. We have one "RFQ" table with records from our sales people requesting a quote for one or many items. We have a separate "Quotes" table that our purchasing employees use to create one quote for each item listed in the RFQ (the quotes and RFQs are related by the RFQ's ID and are visible via a portal on the RFQ record).

                     We have a third table, "Products", that our purchasing team will slowly populate with products. While some items may have a single price regardless of quantity, others may have two, three, or more price breaks. So that is why I had created an "ItemPriceBreaks" table with child records for each price break. These records will record the price break (e.g., 1-100, 101-200, etc.) along with a price.

                     When creating a new quote, I'd like to be able to choose one of these products and then have the option of importing the existing price break lines. I don't simply want to create a relationship to the item's price break records because 1) there is additional decoration costs that needs to be added to that line that is unique to that quote and 2) there item's price may fluctuate. I was thinking that via a script I oould import the item price break lines into a new "QuotePriceBreaks" table with fields for the additional information I'd like to capture.

                     Does that order of operations make sense? I'm open to other suggestions as this is the first major project I've worked on.

                     Thanks in advance for your additional help.

                • 5. Re: Copying Data from One Portal to Another
                  philmodjunk

                       It really doesn't answer the final question:

                       Can your quote list multiple products or will quotes be limited to a single product (but with multiple pricing options) for each quote?

                       This makes a difference in how you display the list of pricing options so that the user can select one, some or all of the options for a given product.

                  • 6. Re: Copying Data from One Portal to Another
                    mperley

                         Right now the plan is to have a one to one relationship between products and quotes. If an RFQ is submitted asking for pricing on several different products, there will be one quote for each product created and all quotes related to the RFQ will be visible via a portal on the RFQ layout. Does that make sense?

                         Thank you for your patience -- I really appreciate the help. 

                    • 7. Re: Copying Data from One Portal to Another
                      philmodjunk

                           That simplifies the process, but keep in mind that this can be made to work form a quote that lists multiple products.

                           Ok, we seem to have these tables to work with Quotes, LineItems, ItemPriceBreaks and Products. RFQ doesn't seem to apply to this issue.

                           Quotes----<lineItems>------Products
                                |                  v-----ItemPriceBreaks
                                |
                                +--------QuoteProducts-----<QuoteItemPriceBreaks

                           Quotes::__pkQuoteID = LineItems::_fkQuoteID
                           Products::__pkProductID = LIneItems::_fkProductID
                           ItemPriceBreaks::__pkItemPBID = LineItems::_fkItemPBID
                           Quotes::_fkProductID = QuoteProducts::__pkProductID
                           QuoteProducts::__pkProductID = QuoteItemPriceBreakds::_fkProductID

                           QuoteProducts is a second Tutorial: What are Table Occurrences? of Products. QuoteItemPriceBreaks is an occurrence of ItemPriceBreaks.

                           See the first post of this thread for an explanation of my notation: Common Forum Relationship and Field Notations Explained

                           This allows you to put a portal to QuoteItemPriceBreaks on your Quotes layout. Quotes::_fkProductID can be set up as a drop down list so that using it to select a product causes the portal to fill with ItemPriceBreak records for that product. You can then place a button in that portal's portal row or select all fields in the portal row and use button setup... to make them into a button that performs this script:

                           Set Variable [$PBID ; Value: ItemPriceBreaks::__pkItemPBID ]
                           Set Variable [$QuoteID ; Value: Quotes::__pkQuoteID ]
                           Set Variable [$ProdID ; Value: ItemPriceBreaks::_fkProductID ]
                           Freeze Window
                           Go to Layout [LineItems ( LIneItems)]
                           New Record/Request
                           Set Field [LineItems::_fkQuoteID ; $QuoteID ]
                           Set Field [LineItems::_fkItemPBID ; $PBID ]
                           Set Field [LineItems::_fkProdID ; $ProdID ]
                           Go TO Layout [original layout]

                           To copy data from ItemPriceBreaks into LineItems, define matching data fields in LineItems and use the Looked Up value option on the auto-enter tab in Field Options to copy the data from the corresponding field in ItemPriceBreaks.

                           An "All price breaks" button can use the List function to get the __pkItemPBID values for all the records in the price breaks portal and then loop through them creating a new record in LineItems for each.

                           And both scripts could be made a bit more sophisticated so that they check for an existing line item record with the same PBID value before adding a new record for that price break.

                      • 8. Re: Copying Data from One Portal to Another
                        mperley

                             This was incredibly helpful. I had some time to work on it tonight and got things 80% of the way (and learned some things too!). However, I seem to have hit a snag when copying the Price Break ID and Product ID from the Item Price Breaks table. Running the script does add a new record in the Line Items table with the correct Quote ID as its foreign key, but the Price Break ID and Product ID fields are not copying over. Since those won't copy over, the data fields I've matched don't populate because the relationship can't be established without that information. 

                             My thoughts are that the issue lies in the Line Items>-----Item Price Breaks relationship, if for no other reason than that is the data that is not copying over. Is there additional settings/configurations that I need to take into consideration? I'd be happy to provide my working file if that would help.

                             Again, I really appreciate your help on this.

                        • 9. Re: Copying Data from One Portal to Another
                          philmodjunk

                               It's my mistake, I referenced the wrong table occurrence.

                               change:

                               Set Variable [$ProdID ; Value: ItemPriceBreaks::_fkProductID ]

                               to

                               Set Variable [$ProdID ; Value: QuoteItemPriceBreaks::_fkProductID ]

                          • 10. Re: Copying Data from One Portal to Another
                            mperley

                            Thanks, Phil, that did the trick and it works great. There's one wrinkle in the setup that is an artifact of how I had set up the Quote layout to begin with. I had two pop up menus; the first to pick a vendor and then a second that is a listing of all products sold by that vendor (we'll likely end up having thousands upon thousands of products so I was looking for a way to narrow that down). However, to get that to work, I had created a Quote Items table, but instead of the relationship Quotes::_fkProductID = QuoteProducts::__pkProductID I had it set up as Quotes::_fkVendorID = QuoteProducts::__fkVendorID. Of course, if I try changing that relationship back to what it originally was it only shows the item price break for the first item that had been entered for a vendor, regardless of the product chosen. Is there a way to preserve this original functionality, or will I need to choose from all items on a quote without first narrowing things down by vendor?

                            • 11. Re: Copying Data from One Portal to Another
                              philmodjunk

                                   To preserve the functionality, use different table occurreces for products. Note that my example has two table occurrences of Products already. You need only add a third occurrence of Products for your relationship based on VendorID.

                              • 12. Re: Copying Data from One Portal to Another
                                mperley

                                     I thought it might be something like that, but I was incorrectly trying to insert the vendor table between the Quote and Quote Products tables but clearly wasn't getting it to work. Just adding a completely different table occurence of Products did the trick.

                                     Do you attend the developer's conference each year? If so, I definitely owe you a drink! Thanks again.

                                • 13. Re: Copying Data from One Portal to Another
                                  philmodjunk

                                       I wish I could, but lack both the finances and (unbelievable but true) do not get paid vacation time that I could use for such if I could afford the cost.

                                  • 14. Re: Copying Data from One Portal to Another
                                    mperley

                                         I hear you. My attendance will depend solely on my ability to persuade my employer that it's worth it.

                                         Still, thanks for the help. It's suggestions/walkthroughs like this that make the prospect of learning FileMaker much less daunting.