?
Solved

Formatting Tab File for the Web that Was Exported from Filemaker List from Portal Records

Posted on 2011-10-26
18
Medium Priority
?
428 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:rvfowler2
  • 8
  • 8
16 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 37034329
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37094715
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37159795
Any suggestion per my tab files?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Expert Comment

by:Will Loving
ID: 37208285
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37208402
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
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 2000 total points
ID: 37208528
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
 
LVL 25

Accepted Solution

by:
Will Loving earned 2000 total points
ID: 37208534
Actually, that should be:

Left, not Leftvalues

Left( "¶¶¶¶¶" ; 5 - valuecount( List ( WRC_GLincome::ExportString ) ) )
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 37216969
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 37217563
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37221158
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 37221246
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37222239
Exporting as a tab file (see screenprint).  And thanks for the formatting tip.
-exporttab.jpg
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 37222683
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 37223284
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
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 2000 total points
ID: 37223481
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
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 37239310
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Introduction to Processes
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question