(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Replacing RTN code in export file
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Replacing RTN code in export file (Read 1825 times)
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Replacing RTN code in export file
Feb 27th, 2018 at 6:28pm
Print Post Print Post  
I have a multi line field on an Invoice for Customer Message. This file gets exported to an IIF file that is imported into QBooks.  The IIF file format is rigid and I cannot modify it.  The problem is that the CM field has Return codes in there, needed for clarity when looking at Sesame record.  But QBooks sees the Return codes as field delimiters and the import fails.

I thought I could replace the RTN codes with a CR/LF combination with OnElementExit code.  I need to replace them before the export happens.

Want to do a Replace(CM,RTN,CR/LF) but can't find the correct code for the RTN and CR/LF codes.  I seem to recall CR/LF might be Chr(10) and Chr(13)?  Have no idea what the RTN might be.

Thanks
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Replacing RTN code in export file
Reply #1 - Feb 27th, 2018 at 6:39pm
Print Post Print Post  
If you are writing the IIF file using Sbasic, you can use @Replace in that event so that the line breaks still remain in the Sesame data(for clarity purposes) but are not written out to the IIF file.

A line break in an element's data in Sesame is the same as @Newline()

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Replacing RTN code in export file
Reply #2 - Feb 27th, 2018 at 7:01pm
Print Post Print Post  
Writing the IIF in Sesame. Element "CustomerMessage" goes into the QBooks field INVMEMO:

Header:
Code
Select All
vHeader = "!TRNS"+@chr(9)+"TRNSID"+@chr(9)+"TRNSTYPE"+@chr(9)+"DATE"+@chr(9)+"ACCNT"+@chr(9)+"NAME"
		+@chr(9)+"CLASS"+@chr(9)+"AMOUNT"+@chr(9)+"DOCNUM"+@chr(9)+"MEMO"+@chr(9)+"TOPRINT"+@chr(9)+"ADDR1"
		+@chr(9)+"ADDR2"+@chr(9)+"ADDR3"+@chr(9)+"ADDR4"+@chr(9)+"ADDR5"+@chr(9)+"TERMS"+@chr(9)+"SHIPVIA"
		+@chr(9)+"SHIPDATE"+@chr(9)+"REP"+@chr(9)+"FOB"+@chr(9)+"PONUM"+@chr(9)+"INVTITLE"+@chr(9)+"INVMEMO"
		+@chr(9)+"SADDR1"+@chr(9)+"SADDR2"+@chr(9)+"SADDR3"+@chr(9)+"SADDR4"+@chr(9)+"SADDR5"+
		@chr(9)+"NAMEISTAXABLE"+@NL()


Transaction:
Code
Select All
// Create TRNS header info for the whole Invoice
		vTRNS = "TRNS"+@chr(9)+Record+@chr(9)+"INVOICE"+@chr(9)+vDate+@chr(9)+"Accounts Receivable"
		+@chr(9)+QBooksName+@chr(9)+vClass+@chr(9)+OrderTotal+@chr(9)+vInvoice+@chr(9)+""+@chr(9)+"Y"
		+@chr(9)+vBadd1+@chr(9)+vBadd2+@chr(9)+vBadd3+@chr(9)+vBadd4+@chr(9)+""+@chr(9)+Terms+@chr(9)+Via
		+@chr(9)+vShipDate+@chr(9)+Slsmn+@chr(9)+FOB+@chr(9)+PO+@chr(9)+vInvTitle+@chr(9)+CustomerMessage
		+@chr(9)+vSadd1+@chr(9)+vSadd2+@chr(9)+vSadd3+@chr(9)+vSadd4+@chr(9)+""+@chr(9)+vTaxable


OK, I think I go it .... something like this:
Code
Select All
var vCM as String
vCM = @Replace(CustomerMessage, @Chr(13), @NewLine)

Then replace "CustomerMessage in the Transaction section (end of line 4)with vCM [/code].  Does that sound right? 

Good solution, less programming on the form.
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Replacing RTN code in export file
Reply #3 - Feb 27th, 2018 at 7:19pm
Print Post Print Post  
No. It'll probably be something like

Code
Select All
var vCM as String
vCM = @Replace(CustomerMessage, @NewLine, "\n")


As both 10(Line Feed) and 13(Carriage Return) will most likely be interpreted by QB as being a break to the next record, you will need to replace them with something else. I believe QB respects '\n' in import files as a Newline but I may be mistaken as it's been a few months since I've written IIF files. If not you can just put a space in it's place.

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Replacing RTN code in QuickBooks export file
Reply #4 - Feb 28th, 2018 at 10:11pm
Print Post Print Post  
Ray the Reaper wrote on Feb 27th, 2018 at 7:19pm:
No. It'll probably be something like

Code
Select All
var vCM as String
vCM = @Replace(CustomerMessage, @NewLine, "\n")


-Ray


Once again, you are correct, Ray. 
This is what works for me:
Code
Select All
vCM = @Replace(CustomerMessage, @NewLine(), "\n")

Works for Sesame data entry, human reading, and QBooks Customer Messages. 
Such a deal!    Thanks.   Smiley
  
Back to top
 
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Replacing RTN code in export file
Reply #5 - Mar 1st, 2018 at 6:55am
Print Post Print Post  
Oops, spoke too soon. 
A closer look in QBooks showed that the "/n" is showing up as text between lines.

So, I tried adding a " " (1 space), and that made the "/n" disappear, but  lines continued as a single long line with no line breaks.

Next I tried adding "     " (5 spaces), and that improved it, but not on all lines.

So finally this worked for me:
Code
Select All
vCM = @Replace(CustomerMessage, @NewLine(), @Text(20, " "))
This provides enough space chars to force the text to the next line.  I think this will work for us.

(Another option now being considered by customer is to make the CustomerMessage box wider (aargh!) in which case I will just need to increase from 20 to another larger number).
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Replacing RTN code in export file
Reply #6 - Mar 1st, 2018 at 2:45pm
Print Post Print Post  
obfusc88 wrote on Mar 1st, 2018 at 6:55am:
"/n"


As long as that Quickbooks field can accept multii-line data, I believe you will want \n not /n

-Ray
  
Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Replacing RTN code in QuickBooks export file
Reply #7 - Mar 1st, 2018 at 4:28pm
Print Post Print Post  
My mistake, Ray (on forum, not in code).  Did not use "/n".

I actually did use "\n", this was the result in the Customer Message box:
Code
Select All
vCM = @Replace(CustomerMessage, @NewLine, "\n")
results in this:

320 Units TOTAL, 40 Unit INCREMENTS\nTESTING SUMMARY RPT\n

Customer Message in QuickBooks now looks like this (same as in Sesame):
Code
Select All
vCM = @Replace(CustomerMessage, @NewLine(), @Text(20, " "))
results in this:

320 Units TOTAL, 40 Unit INCREMENTS
TESTING SUMMARY RPT


Thanks again for the idea to do @Replace here vs. OnElementExit.  Much better solution.
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge