Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 7108 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

Any help is appreciated.

We're trying to import data from an .xls on a bill of materials to simplify pricing. Our current database system can spit out either a single bill or multiple bills in one .xls sheet.

The first field of each record is the "Master Item," followed by "Component." Can you run calculations on data as it's being imported? Everytime the Master Item changes, I need to run a script to create a new record in another table in the FM file.

Posted

Create an "importer" FMP database that works as an intermediate between the Excel file and the real database. The data gets imported into the "importer", the records are processed and validated, then the data is transferred to the real system.

Importers can be set up so that everything happens automatically after the data is sucked-in, or to lead the user through complex processes in a wizard-like linear fashion.

Posted

Everytime the Master Item changes, I need to run a script to create a new record in another table in the FM file.

It sounds like you're attempting to make a Master Item table (one) and a Component table (many)? Does each line identify the Master Item unique ID or serial? If so (and if this is a one-time data migration process), you don't need an import file. Much depends upon 1) whether this is a one-time or ongoing process, 2) how comfortable you are with scripting and manipulating the data and 3) the actual data you are pulling in, ie, if the Master Item ID is in every row.

I agree totally with Vaughan and use Importers on occassion. BUT ... if you import into your many (Components) file and have in place the ability to Find Unique, you can import everything into Components once. Then find the unique entries (one of every Master Item). Once you have your unique set, switch to a table based upon Master Items and import those unique records. You can create your entire Master Item main table at one time.

If there is not already a MasterItem ID or serial on every Excel line, then an Importer is much safer because you will need to loop-script tying the Master Items to their Components before you can move the data into the respective tables.

LaRetta

  • Newbies
Posted

You're pretty close...here's the challenge:

The reason we're doing this is to have a cost/price history of a "Master Item." It is obviously affected by quantity/cost/price of the components. This would be fairly simple, except we also need to keep track of any CHANGING components as well.

2 examples why:

1. We package/assemble kits. When we quote for the customer, we give them our wholesale price to them, and list the "discount from list price" we're giving them--compared to if they bought each individual item separately (it gives them a quick indication of the margins they'll get when they resell). We had a kit where we dropped our wholesale price to the customer, but our "Discount" to them went down. Obviously, there was some part substituted in the kit that affects our cost and discount. We need to be able to see differences between pricing iterations.

2. We actually had a customer that kept asking for modification of quantity and/or components. 7 times in 14 days. We can't keep track of each iteration that we go through. At least, not easily in Excel. Which is our current method.

I've looked at examples from manufacturing/Bills of Material, but we may go through several pricing iterations before the BOM is finally set. We don't want to keep entering and reentering BOMs

armin

Posted

I'm unsure what to suggest, Armin.

but we may go through several pricing iterations before the BOM is finally set.

As in multiple quotes (per Component) before finalized? If the Component changes are listed in Excel, why not just import directly into your Component table? The lines could be flagged as either (quote) preliminary or final and only the final used for your figures. In this way, the variables could also be calculated. All you would need is the Master Item on every Component line in Excel.

Or, if you just need a history and don't need to perform calculatons on the preliminary Components, maybe just use a text field to capture the history (each Component change as lines appended) until finalized. I don't know your current system.

Where's Ugo? He does much of this kind of thing. wink.gif

LaRetta

This topic is 7108 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.