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.
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...
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
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.
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..
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?
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
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):
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?
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!
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!
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.
Thanks very much for your help - I will try that out and let you know how it works, thanks again!