Formatting Tab File for the Web that Was Exported from Filemaker List from Portal Records
In Filemaker, I normalized 5 Income and 10 Expense fields into a separate GL table. However, when exporting these to a tab file, obviously, I get a varied amount of entries for Income and Expenses. It seems my web partner who is importing the tab file (using Pearl) needs exactly 5 spots with tabs inbetween for Incomeand 10 for Expenses. I tried to fix this by using a SendEvent script in FM to run a Visual Basic file I wrote that runs a Word macro that cleans up the tab file. However, I'm still left with some problems.
See embedded pics. See also the file woodrich.tab, which is the old and correctly formatted way of doing it, woodrich2.tab, the new way with the exported portal records, and woodrich2_result.tab, which is what I got after running the FormatWRCexport() Word Macro that you see below. Posting in 3 zones since solution could be in FM, VB, or CGI/Pearl, which is what my partner uses to format the tab file on the web.
-------------
Calc field in question is -- WRCPropertySales::GLExport
Substitute ( List ( WRC_GLincome::ExportString
filewin://upc-fs1/R-DRIVE/
--------------------------
Sub FormatWRCexport()
'
' FormatWRCexport Macro
' Macro recorded 3/15/2011 by
'
ChangeFileOpenDirectory "R:\fmExports\"
Documents.Open FileName:="woodrich2.tab",
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="",
Encoding:=1252
Selection.Find.ClearFormat
Selection.Find.Replacement
With Selection.Find
.Text = "TabhereTabhere"
.Replacement.Text = "Tabhere"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormat
Selection.Find.Replacement
With Selection.Find
.Text = "Tabhere"
.Replacement.Text = "^t"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
End Sub
--ExportingPortalRecs1.JPG
--ExportingPortalRecs2.JPG
--ExportingPortalRecs3.JPG
See embedded pics. See also the file woodrich.tab, which is the old and correctly formatted way of doing it, woodrich2.tab, the new way with the exported portal records, and woodrich2_result.tab, which is what I got after running the FormatWRCexport() Word Macro that you see below. Posting in 3 zones since solution could be in FM, VB, or CGI/Pearl, which is what my partner uses to format the tab file on the web.
-------------
Calc field in question is -- WRCPropertySales::GLExport
Substitute ( List ( WRC_GLincome::ExportString
filewin://upc-fs1/R-DRIVE/
--------------------------
Sub FormatWRCexport()
'
' FormatWRCexport Macro
' Macro recorded 3/15/2011 by
'
ChangeFileOpenDirectory "R:\fmExports\"
Documents.Open FileName:="woodrich2.tab",
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="",
Encoding:=1252
Selection.Find.ClearFormat
Selection.Find.Replacement
With Selection.Find
.Text = "TabhereTabhere"
.Replacement.Text = "Tabhere"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormat
Selection.Find.Replacement
With Selection.Find
.Text = "Tabhere"
.Replacement.Text = "^t"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveDocument.Save
End Sub
--ExportingPortalRecs1.JPG
--ExportingPortalRecs2.JPG
--ExportingPortalRecs3.JPG
ASKER
Sorry, swamped with installing new firewall end of last week and all the new workstations arrive today. Meanwhile, attached are the tab files (just change the .doc extension back to a .tab).
woodrich2.doc
woodrich2-result.doc
woodrich2.doc
woodrich2-result.doc
ASKER
Any suggestion per my tab files?
I'm trying to understand what you are looking for based on your tab files and I'm not sure what you mean, in your original posting, by 5 or 10 "entries" - do you mean fields or tabs, even if the field has no value? Is the goal to have a specific number of tabs, one for each field, even if the field for that record is empty?
As mentioned earlier, you can use a calculation such as the one your are currently using to create a single concatenated field which has tabs between each value. If some fields are empty you may need to add conditions to the calculation that account for that and put a tab in anyway. In either case, YOU are inserting the tabs in the calculation (just copy a "tab" from Word and paste it in in quotes in the calculation) rather than using VB and the "tabhere" substitution. When you export out, you export JUST that one concatenated field as CSV (not tab) and then change the extension to tab.
All that may be more than you need so let's start with my first question about "entries"...
As mentioned earlier, you can use a calculation such as the one your are currently using to create a single concatenated field which has tabs between each value. If some fields are empty you may need to add conditions to the calculation that account for that and put a tab in anyway. In either case, YOU are inserting the tabs in the calculation (just copy a "tab" from Word and paste it in in quotes in the calculation) rather than using VB and the "tabhere" substitution. When you export out, you export JUST that one concatenated field as CSV (not tab) and then change the extension to tab.
All that may be more than you need so let's start with my first question about "entries"...
ASKER
Before normalization, we had 5 income and 10 expense fields, which all were included in our export (tab delimited) script step. Even if one of these fields was blank, it would still have a tab before and after it. It seems my web guy needs a fixed number of fields and tabs when importing the tab delimited file to the web.
However, when I normalized these income and expense fields to a different table and brought them back in as a portal, I only export the number records (with tabs between them) as I have entries. If have have 3 income and 7 expense entries, then I only have 3 pieces of income data and 7 pieces of expense data that will be exported; thus a total of 10 pieces of data with tabs between them whereas in the old method, I would have 5 income and 10 expense pieces of data with tabs between them no matter if the field was blank or had a number in it.
From his perspective, my web guy needs 15 fields exported always (although I am also posting this in the web zone to see if there is a way of doing it differently from his end). Hope this clarifies.
However, when I normalized these income and expense fields to a different table and brought them back in as a portal, I only export the number records (with tabs between them) as I have entries. If have have 3 income and 7 expense entries, then I only have 3 pieces of income data and 7 pieces of expense data that will be exported; thus a total of 10 pieces of data with tabs between them whereas in the old method, I would have 5 income and 10 expense pieces of data with tabs between them no matter if the field was blank or had a number in it.
From his perspective, my web guy needs 15 fields exported always (although I am also posting this in the web zone to see if there is a way of doing it differently from his end). Hope this clarifies.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost, but not quite working. I tried using an InsertTab function, as you once suggested, pasting in a tab into this Custom Function. However, it seems this does not carry through when doing a list from one table to the next. I got spaces in between my fields instead of tabs. Then I decioded to put the text "InsertTab" and then substitute this in the main table for the function InsertTab so a tab space appears, but this didn't work either. This formula below did not do a second substitute but left the InsertTabs in place. See below that the text and the way I would like it to be:
-------------------
WRC_GLedger::ExportString:
If (
IncExpFlag = "Income" ;
Description & "InsertTab" & Amount & "InsertTab";
Description& "InsertTab" & Amount & "InsertTab"
)
-----------------
GLExportIncome::::
Substitute (
Substitute (
List ( WRC_GLincome::ExportString
Case ( ValueCount ( List ( WRC_GLincome::ExportString
; "¶" ; InsertTab )
; "InsertTab"; InsertTab )
-----------------------
CO 730 400 SHOPPING CENTER FOR SALE 50 Middle Country Rd. Coram NY 11727 Suffolk 1 Story Masonry Building with 11 Stores Ample Front Parking 52,616 Sq. Ft. (1.2 Acres) 6,540 Square Feet $138,597.00 Real Estate Tax: $25,725.00 Insurance: $2,049.00 Water: $431.00 Cleaning: $3,660.00 Repairs: $3,671.00
--------------------------
CO 730 400 SHOPPING CENTER FOR SALE 50 Middle Country Rd. Coram NY 11727 Suffolk 1 Story Masonry Building with 11 Stores Ample Front Parking 52,616 Sq. Ft. (1.2 Acres) 6,540 Square Feet $138,597.00 Real Estate Tax:InsertTab25725InsertTa
-------------------
WRC_GLedger::ExportString:
If (
IncExpFlag = "Income" ;
Description & "InsertTab" & Amount & "InsertTab";
Description& "InsertTab" & Amount & "InsertTab"
)
-----------------
GLExportIncome::::
Substitute (
Substitute (
List ( WRC_GLincome::ExportString
Case ( ValueCount ( List ( WRC_GLincome::ExportString
; "¶" ; InsertTab )
; "InsertTab"; InsertTab )
-----------------------
CO 730 400 SHOPPING CENTER FOR SALE 50 Middle Country Rd. Coram NY 11727 Suffolk 1 Story Masonry Building with 11 Stores Ample Front Parking 52,616 Sq. Ft. (1.2 Acres) 6,540 Square Feet $138,597.00 Real Estate Tax: $25,725.00 Insurance: $2,049.00 Water: $431.00 Cleaning: $3,660.00 Repairs: $3,671.00
--------------------------
CO 730 400 SHOPPING CENTER FOR SALE 50 Middle Country Rd. Coram NY 11727 Suffolk 1 Story Masonry Building with 11 Stores Ample Front Parking 52,616 Sq. Ft. (1.2 Acres) 6,540 Square Feet $138,597.00 Real Estate Tax:InsertTab25725InsertTa
It looks like the Substitute function is not swapping the InsertTab function for the "InsertTab" text string. Try copying a tab directly from a word document so that you get an actual tab character and the use that, in quotes, as the replacement string in the Substitute function instead of the InsertTab custom function.
ASKER
After a lot of testing, it seems that Filemaker just does not export a tab character; I guess it uses that for separation of fields. I exporteed "InsertTab" for every tab function, but still have problems with the correct number of tabs appearing. An additional problem is that data pulled through a the List function does not bring formatting; thus my currency numbers are not formatted. What a nightmare. This is my second try at this in 6 months. Don't think I'll ever normalize fields that I have to export again until FM gets user-friendly with tabs.
A question and a comment:
1. What File Type are you exporting as, tab, csv, something else?
2. Applying currency formatting is pretty easy using a custom function. I generally never use the layout based formatting because I want more control. Try this custom function: http://www.briandunning.com/cf/436
1. What File Type are you exporting as, tab, csv, something else?
2. Applying currency formatting is pretty easy using a custom function. I generally never use the layout based formatting because I want more control. Try this custom function: http://www.briandunning.com/cf/436
ASKER
Exporting as a tab file (see screenprint). And thanks for the formatting tip.
-exporttab.jpg
-exporttab.jpg
So, my own experimenting with exporting tabs that are part of a calculation is that you CANNOT export in tab-separated format or you get the problem you are running into with the tabs being converted to spaces.
The way that I have solved this in the past (if memory serves) is to create a SINGLE calculation that concatenates together everything that I want to export including the actual TAB character between each field. You then export JUST that one field as CSV (not TAB) and then change the export file extension to .tab. The tabs should export properly properly and be recognized as field separators.
The way that I have solved this in the past (if memory serves) is to create a SINGLE calculation that concatenates together everything that I want to export including the actual TAB character between each field. You then export JUST that one field as CSV (not TAB) and then change the export file extension to .tab. The tabs should export properly properly and be recognized as field separators.
ASKER
Are you saying you export just that one field into a separate file and then rename it? How do you merge it into the second file? Seems much easier to just name it as InsertTab and then use Word's
VB to do a Find/Replace.
VB to do a Find/Replace.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will, checked my original question and realized I've slid into mission creep, so I'm giving you credit for my original question on how to export and get a static number of fields, which you answered. I think I can take it from here by using Word VB to clean up the tab entries.
Don't know if that helps...