Savings to Excel with Field Title Modified?

[_henry_]

Hello all,

 

I am doing parsing from reports (generic reports parsing). I want to know if FileMaker can modify the Field Title when savings to Excel.

 

For example: I have 3 Fields which are Account Number, Names, and Balance. I would like FM to export to Excel like this:

 

Account Number Names Balance Date

12345 John Doe 123.55 02/02/08

55555 John Smith 15665.66 01/01/08

 

But the unique thing is I should set up the "Field Title" in other fields because the field name can change. For the next report, the field could be like this:

 

Account Number Names Date Misc.

12345 John Doe 02/03/07 Paid

55555 John Smith 05/05/07 UnPaid

 

Is that possible for FM to do that? Or is there workaround? I tried to modify field to TEXT field, however, when I exported to Excel, it would ask to convert to a number field.

 

If you need more information, I can give more explanation.

 

Thank you.

[David Head]

For example: I have 3 Fields which are Account Number, Names, and Balance. I would like FM to export to Excel like this:

 

Account Number       Names              Balance           Date
12345                John Doe              123.55     02/02/08
55555                John Smith          15665.66     01/01/08

 

But the unique thing is I should set up the "Field Title" in other fields because the field name can change. For the next report, the field could be like this:

 

Account Number       Names                    Date         Misc.
12345                John Doe             02/03/07       Paid
55555                John Smith           05/05/07       UnPaid

 

Is that possible for FM to do that? Or is there workaround? I tried to modify field to TEXT field, however, when I exported to Excel, it would ask to convert to a number field.

 

If you need more information, I can give more explanation.

Yes I think you need to give more explanation. You say that you are exporting three fields and then list four fields! So are you exporting four fields? Or where does the other data come from? I can't understand what you are trying to do. Maybe that's just me.smiley-undecided

[_henry_]

Thanks David for responding. The amount of field can be changed according to the reports. Some reports can have 3 fields or more. Usually, it will not more than 10 fields. So, I will create 10 FIELD labels with 10 fields for keeping their records. Does it make sense to you? So, basically I am creating a "generic" reporter. I will give you some examples.

 

First report:

 

Account Number Names Balance Date

12345 John Doe 123.55 02/02/08

55555 John Smith 15665.66 01/01/08

 

So, I will make my Label Field one become "Account Number", Label field two become "Names", Label Field three becomes "Balance" and Label Field Four becomes "Date." Then, I would save all the informations into Data Field One, Data Field Two, Data Field Three, and Data field Four. In here, I supposed to save "Balance" field in Number type, so I can perform calculation in the Excel. However, if I save it in Number as my field type, when I save the Field Title. It would show "0" because I insert a text "Balance" into the my "Balance" field. That's one of example. The thing I could not set a certain Label because sometimes for another report, it would have a different field's name and their own content. But they do have the same padded spaces.

 

If this not clear, please let me know again... thank you for responding smiley-smile

[David Head]

I don't want to give you false hope here. As an entry level FileMaker Pro user what you are trying to do is very advanced. I am sure that I could work out something that involved exporting via a temporary table. But I doubt that you will get there easily. As you say, you are trying to build your own export engine. Big job for a beginner.

[_henry_]

Thank you David for your information. Could you please give some hints? Actually, I am a fast learner... smiley-smile So, if you can give me some guidance, it would be good for me.

 

Thank you for your help. Actually, I can do save the file in Excel, however, the Excel file would only show my "Balance" field's contents as Text not numeric. So, I need to perform convert to Number in Excel (which is a hassle for me).

 

So, if you could give me some hints, it would be helpful smiley-smile

 

Thank you... smiley-smile

[Databasic]

Hi Henry

 

If the Balance field in FM is a numeric field ( which I presumed it would have been ) it will be exported to excel as a general numeric field . Cheers

[Databasic]

PS Henry

 

If you format the number field in FM as currency - it will "copy as excel file" with the currency format --- Cheers

[_henry_]

Thank you Databasics for your answer. However, as I mentioned above, I would like to "insert" my field's title to the field's content.

For example, in the Balance, if you format as numeric which is the best one, when I tried to insert the word "Balance", the word "Balance" would be show only in FileMaker.

 

When I tried to export it to Excel file, it would be converted to "0" not the word "Balance" because of numeric field type. smiley-smile

 

Well, thank you for your help and respond...

[_henry_]

...when I tried to insert the word "Balance", the word "Balance" would be show only in FileMaker.

 

Edit: When I tried to insert the word "Balance" to the first record of the field's content, the word "Balance" would be show only in FileMaker.

[Databasic]

Hi again Henry

 

re-reading your original posting why do you need to parse anything. Are you exporting to excel as a tab delimited file ? FM will open directly in excel ( in excel format) and automatically place the field names as colunm headers. Wouldn't it be easier to export / save as an excel file with all 10 fields and then use various worksheets in the excel file to selectively report the data you want. Cheers

[_henry_]

Hi Databasic,

 

Are you exporting to excel as a tab delimited file ?

No. I am exporting to Excel as it is (regular Excel file). I mean just the regular file with columns and rows.

 

 

FM will open directly in excel ( in excel format) and automatically place the field names as colunm headers.

Here is the problem, if I am using their field names as column headers, it would show only "Field One", "Field Two", "Three", etc. Here's the plan, I am having a Label for every field until field Ten, but it is not necessary for me to use all of them. It depends on the Report that I need to gain the information from. For example, a report only contains 2 columns (Field One and Field Two), and another report can contain 9 columns (Field One, Field Two, ..., Field Nine). So, I need to save their Title header, so when I export to Excel, it would have their header title, otherwise, it only contains data without any field title. If I use their Field names as column headers, on Excel, it would show "Field One", "Field Two", etc.

 

Wouldn't it be easier to export / save as an excel file with all 10 fields and then use various worksheets in the excel file to selectively report the data you want. Cheers

Could you give me more information about this? Every Excel file that I am generating is good for a certain reports. That's why I called this "Generic Reporter." It would handle all different reports with their own unique names of field's header. The unique thing is the reporter that I need to gain information (do the parsing) has the fixed delimited format (padded spaces). For example:

 

"Field one" (10 spaces) "Field Two"

 

------- ( another report )

"Field one" (10 spaces) "Field Two" (10 spaces) "Field Three" (10 spaces) "Field Four"

 

Hope it gives you more information... smiley-smile

 

Thanks for giving out your ideas .... smiley-laughing

[LingoJango]

Only klutzy workaround I can think of is a separate export of just the header fields to another Excel sheet, and an Excel macro that would replace the generic field labels at the top of the column with the header field contents.

[Databasic]

Sorry Henry I still don't get it !!

 

If I Send/Save and FM file as Excel the resultant excel file opens with the FM field names AUTOMATICALLY placed into the column heads... so if you save all 10 Fields you get a spread sheet that contains all your data. Then to produce various reports - you add a new worksheet into the excel file and copy/paste the columns you need for that report. To automate the process you may need to use a formula such as in Worksheet2- A2 the formula =Worksheet1!A2. requires a little set up but once done you have all the reporting tools from excel available

[_henry_]

Hi Databasic,

 

That's okay. Maybe I am not explaining well... smiley-tongue-out

 

 

If I Send/Save and FM file as Excel the resultant excel file opens with the FM field names AUTOMATICALLY placed into the column heads... so if you save all 10 Fields you get a spread sheet that contains all your data.

 

The problem with this, on my Excel Spreadsheet, it would look like this:

 

Field One Field Two Field Three

12345 John Smith 123.50

56788 John Doe 88.25

 

instead of:

Account Number Full Name Balance

12345 John Smith 123.50

56788 John Doe 88.25

 

The thing is I want to do everything automatically. When I press the button, it would process the Reporter file directly (without human intervention). It would directly process everything and save to excel file...

 

Thanks for your comment...smiley-smile

[_henry_]

Hi LingoJango,

 

Only klutzy workaround I can think of is a separate export of just the header fields to another Excel sheet, and an Excel macro that would replace the generic field labels at the top of the column with the header field contents.

 

Could you give more info about klutzy workaround?

 

Can you perform the "excel macro" from FileMaker? Like send event stuff?

[LingoJango]

Don't know how to trigger an Excel macro from FileMaker.

 

However, it shouldn't be too hard to open the Excel file containing the macro (that much FM can do), open the exported Excel file, and then execute the macro.

 

How to make the macro. You say you have

 

Field One Field Two Field Three

12345 John Smith 123.50

56788 John Doe 88.25

 

but in fact from your description you can probably export

 

FieldOne FieldTwo FieldThree FieldFour... TitleOne TitleTwo TitleThree TitleFour ...

12345 John Smith 123.50 (blank) Account Number Full Name Balance (blank)

56788 John Doe 88.25 (blank) Account Number Full Name Balance (blank)

 

So, if you always export every single field, whether or not it is empty, all you need is a macro that will set A1:d1 to the values of E2:H2. (That's if you have four content fields and four title fields, of course. Just noticed you have ten, so that would be A1:J1 and K2:T2)

 

I'm no Excel whizz, but it sounds like the stuff of recorded macros.

[_henry_]

Hi LingoJango,

 

 

but in fact from your description you can probably export

 

FieldOne FieldTwo FieldThree FieldFour... TitleOne TitleTwo TitleThree TitleFour ...

12345 John Smith 123.50 (blank) Account Number Full Name Balance (blank)

56788 John Doe 88.25 (blank) Account Number Full Name Balance (blank)

 

You got my idea... smiley-smile

 

Well, anyway, thank you for your comment... smiley-smile

[Databasic]

OK Henry last try...

 

HAve you tried to send/save to excel ? If you do, the field names from FM become the column headers in excel . So If you have fields in FM like Date, Balance, ect they will automatically become the column headers. The only way you would get the result you suggest is if your fields in FM are named Field1, Field 2 ect. which i'm sure they are not. Good luck

[_henry_]

Hi Databasic,

 

Thank you for your effort smiley-smile

 

The only way you would get the result you suggest is if your fields in FM are named Field1, Field 2 ect. which i'm sure they are not.

Well, my fields' name in FM are Field1, Field2, etc. Because I am generating a "generic reporter." Also, I have other fields as LabelField1, LabelField2, etc.

 

The thing is like this:

 

LabelField1 LabelField2 LabelField3

Field1 content Field2 Content Field3 Content

 

In here, I cannot make Field3's title name as "Balance" because how about in my another reporter, on the field 3, maybe the field's title name become "Date"? That's why I need to make it generic FieldName.

 

For sample above, if I am exporting to excel, it would be:

 

LabelField 1 LabelField2 LabelField3 Field1 content Field2 Content ...

Account Name Balance 12345 John Smith...

Account Name Balance 77777 John Doe...

 

That's the odd thing that would show on excel. So, I am "inserting" all the LabelField's name to my Field's contents. The problem with this is if I exported on Excel. For example, the "Balance" field would be "numeric", but I will insert "text" type for the title "Balance" on the top of the record. On FM, it would show just fine, but when exporting to excel, it will show "0"....