12 Replies Latest reply on Oct 2, 2013 12:04 AM by incybau

    Importing excel data into a portal of one record

    incybau

      Title

      Importing excel data into a portal of one record

      Post

           Hi, Is there a way to import some line items into a portal of one record?  Specifically, we are a printing company and we get requests for cost estimates.  So, instead of copying and pasting each line from the email, I was hoping to give them an excel template where they fill up what they need estimated and we import that into the portal lines of the cost estimate being prepared for them.

           Our cost estimate database includes a  portal with 7 columns - PRODUCT (which is a drop down to choose the item), QTY, DESCRIPTION, SIZE WIDTH, SIZE HEIGHT, UNIT COST, TOTAL.  What I was hoping to do was provide the excel template when they email in their request and fill up the PRODUCT, QTY DESCRIPTION, WIDTH, HEIGHT.  Once we have that, we import the data into the new record/Cost estimate and we plug in the unit cost, the TOTAL is already a calculation.

           Our database is pretty complete, we have a customer database, product database linked to each cost estimate, it just gets tedious when inputting the line data when sometimes they can reach up to 50 lines per cost estimate.  I was just trying to avoid less mistakes which happens when you copy and paste, that way the data  stays intact.  Is this possible?  Hoping someone can point me in the right direction.  

           Thank you for any help provided

            

        • 1. Re: Importing excel data into a portal of one record
          philmodjunk

               Data from Excel files can be imported directly into your portal's table. You can do this manually from a layout based on the portal's table or you can create a script that does it all at the click of a button. After importing, Replace Field Contents--which can also be part of the script can update the match field that links these records to the correct parent record.

          • 2. Re: Importing excel data into a portal of one record
            incybau

                 Thank you - Is there any area here that I can see examples - I try to import files but all other related tables are greyed out so I'm not sure how to do it...

            • 3. Re: Importing excel data into a portal of one record
              incybau

                   I was able to import it to the layout of the portal (line items table) but when I go back to the main Form table of the cost estimate, the items don't come out in the portal or I don't know how to refer those imported items in the layout of the line items to the portal of the cost estimate. 

                   Thank you again for the help

              • 4. Re: Importing excel data into a portal of one record
                philmodjunk

                     The imported records won't be automatically linked to the layout's record. That's why you need to do this:

                     

                          After importing, Replace Field Contents--which can also be part of the script can update the match field that links these records to the correct parent record.

                • 5. Re: Importing excel data into a portal of one record
                  incybau

                       Thank you again - Sorry to be a bug but  I'm really not very proficient - is there an area in this forum that can show me step by step?  I need to understand how it is manually before I get into the scripts..

                  • 6. Re: Importing excel data into a portal of one record
                    philmodjunk

                         You are in the right place, but I know almost nothing of your database. The details depend on the design of your layouts, tables and relationships.

                         Describe the relationship you have in place so that your layout can have this portal. What are the match fields used in that relationship?

                    • 7. Re: Importing excel data into a portal of one record
                      incybau

                           Okay, I drew out a chart of our database (attached) - The match fields that tie them in are the Client's ID and in the Line portal, it's the product ID that ties it together.  I didn't create the database, and I have a little above beginner knowledge on filemaker  so I can tinker a little bit to  improve on it but I do get lost with scripting a lot.  I found the layout of the line item portal, that's where I tried importing the excel file, I got that far but was stumped after that.  

                           There are a lot of other tables in our database but that's mostly for our billing based on the cost estimate.  The heart of all our data really comes from the Cost Estimate but I just wanted an easier way to encode the data in the line items of our cost estimates because we do end up cutting and pasting from emails for every single line.  Some cost estimates go up to 10 pages long.

                           Thanks again for the help - really appreciate it

                      • 8. Re: Importing excel data into a portal of one record
                        philmodjunk

                             But what match fields link Estimates to Line Items?

                             A simpler approach that would have shown more of the key details would have been to do a screen capture of Manage | Database | Relationships, crop it down to just those that apply to this issue and upload it with the "upload an image" controls shown below Post A Answer.

                             So I read that as 4 tables with these relationships (and these are pretty standard for this type of thing):

                             Clients----<Estimates-----<LineItems>-----Products

                             And the key relationship match fields are probably:

                             Estimates::__pkEstimateID = LineItems::_fkEstimateID
                             Products::__pkProductID = LineItems::_fkProductID

                             But your names for tables and fields are likely a bit different.

                             For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                             Is this what you have?

                             In the data that you are importing from Excel, does it contain the needed ID for each product/material?

                        • 9. Re: Importing excel data into a portal of one record
                          incybau

                               Yes, so sorry - I'm not really thinking like a data developer - I'm attaching the screenshot of the relationship table - there's just a lot of other stuff there like where it ships to, etc.  hope this makes it clearer, thank you again!

                          • 10. Re: Importing excel data into a portal of one record
                            incybau

                                 Forgot to answer :  The data imported from Excel does not have to have any product ID just the qty they need, the description (this is just for project purpose, no connection to materialm for example they'll call it Store Window) and the size of the width and height.  We choose the product ID when they email their request because material will differ depending on the type of project - thanks so much again!

                            • 11. Re: Importing excel data into a portal of one record
                              philmodjunk

                                   Best guess is that Layout Setup... for your layout with the portal shows EstimatesAndInvoices in Show Records From and that Portal Setup Shows OrderItems in Show Related Records From for your portal.

                                   The key detail this last post provides is that you have two pairs of match fields linking EsimatesAndInvoices to OrderItems. Your imported data will not show up in your portal until both OrderItems::InvoiceID and OrderItems::LineItemType are given the correct value on each imported record.

                                   You could copy InvoicePKID from EstimatesAndInvoices before importing, then Paste this value into Invoice ID and then use Replace Field Contents to copy this to all the other OrderItems records just created by the Import. But you will also need to get the correct value into LineITemType. It's possible that this field auto-enters a default value and you just need to enable auto-enter options during the import to get that value. Or you may need to use a second Replace Field Contents to update it as well. You'll have to figure out what this value is whether or not it might be an auto-entered value or not.

                                   And of course a script can be set up to manage all these details for you.

                              • 12. Re: Importing excel data into a portal of one record
                                incybau

                                     Thanks very much for your help - I will try that out and let you know how it works, thanks again!