Sage 100

Is there a secret to getting the mask/format to work in a VI Export, i.e. commas, dollar signs?  We have the default mask (###,###,###.00) in place for a numeric/sales field, but it just exports 'raw' - via v2018 trying various file types/settings.

------------------------------
- Brett
------------------------------

The "secret" I use is: avoid VI Exports like the plague.  Other methods of extracting data (custom report, Visual Cut, vbscript...) are much more flexible and reliable.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Lol, agreed, @Kevin Moyes.  We're currently leaning toward Crystal / Visual CUT​.

------------------------------
- Brett
------------------------------

Original Message:
Sent: 05-01-2019 13:40
From: Kevin Moyes
Subject: VI Export mask/format - Is there a secret to get it to work?

The "secret" I use is: avoid VI Exports like the plague.  Other methods of extracting data (custom report, Visual Cut, vbscript...) are much more flexible and reliable.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

VI Exports do some things OK but this one not so well. Until #VisualCUT is available to client this might work.

The mask is totally ignored (demonstrating Kevin's point) until you do something to treat your numeric field as string. E.g. If you check the box Pad Field with Spaces, it then observes your mask but also put spaces in front so that sucks.

Another way is use a Temp field, keep it as String, keep Operation as Assigned, add a Calculation Expression like this:

STP(STR(AR_Customer01.CreditLimit:"#,###,###,###.00-"),2)

This observes the mask in the line. It works but surrounds the field with double-quotes in the export file whilst the other fields are not quoted. I cannot just strip the quotes here b/c they are not added until a later stage.

To be uniform so ALL fields are Quoted you could:
* Go to Configuration tab click Options button, click box for Wrap String Fields in Quotes.
* Do same trick above for other numeric fields you are exporting

To be uniform so ALL fields are UnQuoted you could:
Add some Perform Logic on the Before Write event to strip any quotes found.​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

Original Message:
Sent: 05-01-2019 14:51
From: Brett Zimmerman
Subject: VI Export mask/format - Is there a secret to get it to work?

Lol, agreed, @Kevin Moyes.  We're currently leaning toward Crystal / Visual CUT​.

------------------------------
- Brett
------------------------------


Original Message:
Sent: 05-01-2019 13:40
From: Kevin Moyes
Subject: VI Export mask/format - Is there a secret to get it to work?

The "secret" I use is: avoid VI Exports like the plague.  Other methods of extracting data (custom report, Visual Cut, vbscript...) are much more flexible and reliable.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Thanks, @Alnoor Cassim.  Really appreciate the info.​

------------------------------
- Brett
------------------------------

Original Message:
Sent: 05-02-2019 03:30
From: Alnoor Cassim
Subject: VI Export mask/format - Is there a secret to get it to work?

VI Exports do some things OK but this one not so well. Until #VisualCUT is available to client this might work.

The mask is totally ignored (demonstrating Kevin's point) until you do something to treat your numeric field as string. E.g. If you check the box Pad Field with Spaces, it then observes your mask but also put spaces in front so that sucks.

Another way is use a Temp field, keep it as String, keep Operation as Assigned, add a Calculation Expression like this:

STP(STR(AR_Customer01.CreditLimit:"#,###,###,###.00-"),2)

This observes the mask in the line. It works but surrounds the field with double-quotes in the export file whilst the other fields are not quoted. I cannot just strip the quotes here b/c they are not added until a later stage.

To be uniform so ALL fields are Quoted you could:
* Go to Configuration tab click Options button, click box for Wrap String Fields in Quotes.
* Do same trick above for other numeric fields you are exporting

To be uniform so ALL fields are UnQuoted you could:
Add some Perform Logic on the Before Write event to strip any quotes found.​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------


Original Message:
Sent: 05-01-2019 14:51
From: Brett Zimmerman
Subject: VI Export mask/format - Is there a secret to get it to work?

Lol, agreed, @Kevin Moyes.  We're currently leaning toward Crystal / Visual CUT​.

------------------------------
- Brett


Original Message:
Sent: 05-01-2019 13:40
From: Kevin Moyes
Subject: VI Export mask/format - Is there a secret to get it to work?

The "secret" I use is: avoid VI Exports like the plague.  Other methods of extracting data (custom report, Visual Cut, vbscript...) are much more flexible and reliable.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON