I'm fairly new to Filemaker 10, but I've used Filemaker in the past.

I've got several tables that I have setup in relationships that I pull into one layout for a report. The tables I'm using are:

• Item_Master
• Item_Pricing
• Item_UOM
• Item_Companion
• Item_Class_Filters

I have a list layout called "Price List" that utilizes all of the related tables above to build a list of items, prices and various item details. A couple of finds/omissions are performed and then a final sort via a script. Once we have the correct found set in that layout, the ultimate goal is to export it to an Excel file with headers.

Unfortunately, Filemaker doesn't create user friendly headers when exporting to excel. So, since I have multiple realtionships in that layout, when I export I get headers that look like:

• Item_Pricing::item_price_standard
• Item_Pricing::item_price_wholesale
• Item_UOM::item_uom_qty
• etc...

That just won't work for our users. They want the headers to be user friendly without the need for manipulation after exporting.

So, I thought I could add two more tables:

• Excel_Export
• Excel_Export_Header

The Excel_Export_Header table will contain only one record that I will store the field headers in.

Then I created a script to delete all records in the Excel_Export table and import the only record from Excel_Export_Header. So, now I have one record in Excel_Export, which is the field headers.

My next step would be to import my found set from my "Price List" layout into the Excel_Export table. Then, I would have a table whose first record is my headers and the remaining records would be from my import. I could then export that final table to an excel file, with my custom headers.

So... I get stuck at the point where I want to import my found set from the Price List layout into my Excel_Export table. I can't seem to find a script step that will accomplish this without the use of loops and I'm really hoping to avoid that situation.

Is there some preferred Filemaker technique to accomplish this? Perhaps my two-table approach is just overkill and there is something else I'm missing?

Any help would be greatly appreciated!

Can you post a clone with some sample data? and an example of the final Excel worksheet?

Sure. I've stripped all the sensitive bits from my database so you can take a look.

I'm ultimately just wanting to export the data in the "Price List" layout to an Excel file with custom field headers.

You'll see a script called "Export to Excel" that is a mess. It's a work in progress. Right now, it's broken. I can't seem to find a way to export a dataset in a layout to another table in the same file.

The final excel output file should have fields and data mapped identically to the "Price List" layout, with the first record being field header names. The field header names are defined in the "Excel_Export_Headers" table that is only visible from the Layout mode.

Thanks in advance!

Pricing.zip

Edited June 9, 200916 yr by Guest

I'll need a couple of days. Anyone else is welcome to jump in.

Wow, I just don't know if it's me, or this structure isn't correct. I've gone down the road to see if the structure is correct, because that'll end up with a better approach to the export. I don't see why you need the table Item_Companion. Why can't those be fields in Item_Master.

For example, for Item #12345, you have on your Price List its UM to be DS. Yet the prices are reflecting Pricing for Each not by UOM.

I would have the Pricing table contain:

__kP_PriceID

_kF_ItemID

UOM

Type (Standard, Wholesale, etc.)

Price

For nice export, you could create nicely named calc fields in ItemMaster, and export them.

I'd (we'd) welcome other input.

Yeah, the Item_Companion table isn't *technically* needed. I'm importing all of this data directly from tables in another database and that's the way the tables were originally setup. I suppose I could do a join query when importing the data and get everything into Item_Master, but I'm not really concerned about that part of my database.

I'm really just looking for a way to export the data in the "Price List" layout to another table in the same file.

Right now, the only thing I can think to do is to export the data to a separate delimited file and then re-import from that file. That's not really an ideal solution.

What about the problem I saw?