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::GLExportIncome
Substitute ( List ( WRC_GLincome::ExportString ) ; "¶" ; "Tabhere" )

filewin://upc-fs1/R-DRIVE/fmExports/woodrich2.tab

------------------------------------------------
Sub FormatWRCexport()
'
' FormatWRCexport Macro
' Macro recorded 3/15/2011 by
'
   ChangeFileOpenDirectory "R:\fmExports\"
    Documents.Open FileName:="woodrich2.tab", ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="", _
        Encoding:=1252
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    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.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    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
LVL 2
rvfowler2Asked:
Who is Participating?
 
Will LovingPresidentCommented:
Actually, that should be:

Left, not Leftvalues

Left( "¶¶¶¶¶" ; 5 - valuecount( List ( WRC_GLincome::ExportString ) ) )
0
 
Will LovingPresidentCommented:
I don't have time to download the dig into this right now (and I don't think you actually included the tab files) but one way I've dealt with things like this is by creating a calculation field  that concatenates together various fields using a pasted in tab character and then exporting as CSV, but importing as tab. Since FM normally doesn't support entering a tab character in a calculation, you have to copy it from somewhere else like Word and then paste it in. Some people even create a Custom Function called "Tab" which is nothing more than a tab character in quotes.

Don't know if that helps...
0
 
rvfowler2Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rvfowler2Author Commented:
Any suggestion per my tab files?
0
 
Will LovingPresidentCommented:
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"...
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
Ok, so what you mean is that you previously had a single record (Property?) with all 15 income and expense fields and when you exported you would get all of them regardless of whether a particular category had an entry. Now you have a related table with essentially just the amount and description, correct?

So how about something along this line:

Substitute (
List ( WRC_GLincome::ExportString ) & 
Case( valuecount(  List ( WRC_GLincome::ExportString ) < 5 ) ; LeftValues( "¶¶¶¶¶" ; 5 - valuecount( List ( WRC_GLincome::ExportString ) ) ) )
; "¶" ; "Tabhere" )

I haven't tested this calc to make sure there are no typos, but what it does is concatenate onto the end of your export string a up to five paragraph returns which are then converted to "TabHere" by your Substitute function. If you have two entries, it will then be followed by three "TabHere"
0
 
rvfowler2Author Commented:
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 ) < 5 ) ; Left ( "¶¶¶¶¶" ; 5 - 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:InsertTab25725InsertTab Insurance:InsertTab2049InsertTab Water:InsertTab431InsertTab Cleaning:InsertTab3660InsertTab Repairs:InsertTab3671InsertTab
0
 
Will LovingPresidentCommented:
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.
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
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
0
 
rvfowler2Author Commented:
Exporting as a tab file (see screenprint).  And thanks for the formatting tip.
-exporttab.jpg
0
 
Will LovingPresidentCommented:
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.
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
I'm saying that you create a single "Export" field, concatenating all the fields you want to export together with a tab character between each one. Then export just that one field as CSV and then change the file extension. There may be simpler ways to solve your problem but I'm just saying that if you export as tab-separated, then FM WILL convert any embedded tab characters into spaces so as not to mess with the data structure.
0
 
rvfowler2Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.