Field Names Controversy

[LaRetta]

I am on the verge of giving in to Management. We generate quite a few exports either as .mer or Excel. And, no matter how I name my fields, they want the output field names 'nice.' They consider cCustomerSince as not nice. Or _pk_ContactID or BusinessType or Price_Base ... get the picture?

They are tired of modifying the field names in Excel (to make them look nice by removing *my* stuff and adding the spaces back in) before they send the spreadsheets on to various businesses. I understand their point of view. Has anyone else faced this dilemma and is there anything I can do to make us ALL happy?

All I can think of is to create new tables with THEIR field names and script pulling the data into those tables then exporting from there. Lord help me ... :crazy2:

[musicarteca]

This is just a wild shot, but it might work. If you don't select "use field name as column names in first row", the title row will be the first record, so how about creating a new invisible record that always sorts first, with the nice names for every field.

Edited March 26, 2006 by Guest

[LaRetta]

Thank you! That is worth playing with. I would sure hate to hard-code script or calcs. Possibly I could use a conversion table to hold the substitution names. Create new record, set the fields with substituted conversion name. I could place the Conversion Field and Field Name so Management can rename to their heart's content. Many more table occurrences in my graph for this relationship to many tables.

Wait. It wouldn't need to be a related table at all if I place the values in globals ...

[musicarteca]

Wait. It wouldn't need to be a related table at all if I place the values in globals ...

Globaly things look nicer now. :smile2:

Edited March 26, 2006 by Guest

[Fenton]

There is also XML export, which lets you name the "fields" whatever you want. You just select the 1st ROW and list your names, separated by tab; comma-separated can also be done, but is a little more work, since it needs only a comma at the beginning and end. I have a couple of generic xsl stylesheets that will output either a tab or comma-separated file, from any FileMaker table; but without field names. You'd have to write out the names you wanted. But it's not all that hard. The xsl file could be on either a central file server, or an always available web site.

Of course, this would mean that exports would have to be scripted. It is possible to use script Variables to hold the xsl path, accessed via the calculation option for the file path. So, it would be possible for a script to flip to the correct xsl for a known export.

Yes, it's some trouble. But so is creating a bunch of calculation fields to mirror the real fields, or setting fake data into the 1st record (which also requires scripted exports). At least xml/xsl leaves your FileMaker files alone. Here's an example; only 3 fields in the "header" right now, "Field1", "Field2", "Field3". But you could easily add more (it has a Unix Unicode line feed ending character, add a return before it for PC-specific),

<?xml version="1.0" encoding="utf-8"?>



xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >









Field1

Field2

Field3



































 



 





 

[Kent Searight]

Since you already have Excel sheets that have had the first row renamed, you could create templates from them that only hold data (column titles) in the first row. You can then import data that's been exported from FM (w/out field names, of course) starting at the second row. I just tried it out and it worked easily. With a little VB or AppleScripting I'd imagine you could make it fully automated.

It should be noted though that this carries the risk of importing the wrong data for the wrong column if changes are made to the FM db or to the Excel Template without corresponding changes made to the other. Although Fenton's way is more complicated to set up, it appears to be negate that risk.

That's my ...hope it helps!

[LaRetta]

Thank you all for the ideas!

You've given me much to consider. I've hesitated responding because I'm still working through the options. I didn't want you to think I missed the thread nor that I didn't appreciate the response. I'll let you know what I finally figure out - I have some learning to do first ... :smile2:

[LaRetta]

Alright. Confession. Fenton, when I read the xsl I felt the color leave my face. I've struggled with this before. And I ALMOST threw my internal hands in the air but stopped myself. If I don't learn this now, when will I?

Kent, I want to increase my knowledge of FM/Excel interaction as well.

Opportunities can sometimes make us turn green but ahhh, how sweet when we finally figure them out. Wish me luck!

L

[xochi]

I've faced this problem too, and I tend to do one of two things:

1. Create "shadow" export fields in my table which are just unstored calc fields with the "good" name, e.g. "Student Identifier" = pk_StudentID. Then I make sure I organize the fields in my table clearly, using dumy fields to separate the "real" fields from the "export" fields. Pros -- easy to do, no scripting required, minimal performance hit. Cons -- in a table with lots of fields, this effectively doubles the number of fields, and can be frustrating if you are a developer (dealing with all those fields).

2. Create a shadow export table -- this is simply a table with one real field (the primary key of the table you wan to export). Then, you have to add a small script to do exports: "Delete all records, import all records (primary key only), export". Pros -- keeps your original table from being mucked up with tons of extra fields. Cons -- requires import step, requires another table and TOG.

I'm tending to prefer #1 these days -- although it's a pain to set up at first, once you have done it it's pretty nice. It also has the added advantage of providing a layer of indirection -- you can change the underlying fields w/o messing up the export, and the export fields can also pull data from other tables.