Overview:

Baan provides an "Exchange" module which is a facility for batch communication between Baan tables and other sources of data (ASCII files). While this is a powerful tool, it falls short in two areas, which I have enhanced. One, it does not provide a facility for testing exchange schemes without actually loading the data. Two, though it produces error messages, their vagueness often requires developers to waste valuable time in resolving them.

Solution: Validate Import Exchange Data (tuxch9223m000):

This session is a pre-processor for validating ASCII input files. Since it is a pre-processor, data is validated without being inserted into a table. This saves considerable time as it avoids loading and reloading company data each time an exchange scheme is tested. With this pre-processor, the exchange scheme test cycle can be performed until all errors are resolved.

The validation process is designed for import exchange schemes, only. Software contained in exchange scheme conditions are not processed and are not in scope for this session. The following are examples of the error level checks performed by the pre-processor:

Exchange Scheme Level Validations

The input ASCII file exists in the Data Path directory and it is accessible.


Record Level Validations

The data on the input record doesn't already exist in the table.
The number of delimited fields on the record matches the ASCII file definition.
Valid text header lines with data fields are defined with a text domain.
Text trailer lines exist that correspond to the text header.


Field Level Validations

Fields defined as numeric can only have the follow characters "01234567890-+."
Fields defined as numeric can not be blank or (null).
Fields defined as dates have the format defined in the Exchange Scheme.


Reference Level Validations

All reference data in fields, including combination fields, that are not blank and have a reference to another table, actually exists in that table. (No more 606 errors).


Attached zip file includes:
tuxch9223.bc - the session source code
tuxch9223.doc - Word 97 information document

Enjoy!

Hi expert,

I am trying to use this code. This thread is already so old.
Does anyone know how to create the report for this session.

I have created the form and added the script. But do not know how to create the report because there is no sample here. What fields should
I add and in which layout?

Thanks and regards,

Attached is a zipped word document of some screen prints for the report we have. All it has is some screen shots of the input fields, the layouts and a couple of shots of the layout display.

Hi Mark,

Thank you for the layouts. Appreciate it

regards,

Hi Mark,

I don't know whether you have the time to test the program at your place.

But for me, after I copied the whole program into my env and run it.
This message appears:

Invalid SQL Error 301 (Query not correct on SELECT.SQL=<SELECT tcmcs012.uset, tcmcs012.stgu from tcmcs012 WHERE tcmcs012._comprn=960 and tcmcs012._index1 = {"SET1","PCS"}>


The field name is wrong for tcmcs012.stgu as there is no field name stgu in tcmcs012

What do you think is wrong. Can you correct the program?

Thanks and regards,

The version attached to this post is a little different than the version Gordon left with us. I do know that we only use it to validate full table loads(all fields on table in the data file). I know it does work. I am attaching our version. I would recommend putting the other version in debug mode and finding what is creating that bad sql statement. Is tcmcs012 the table you were loading? Or is it another another table with a reference to tcmcs012? If so then it is probably in the does.reference.data.exist() routine.

Hi Mark,

I tried the program using your attached file. It runs without the error message. But then I noticed you commented out the messages of "Invalid SQL"

Using the first program, I also will not get the error message if I comment out the "Invalid SQL" message statements.

So actually why did you comment out the messages. If you do not comment out, the error message will also appear, if i use your program. So it is suppose to be commented out or not??

thanks and regards,

Actually I have never worked on the session. I just knew that Gordon left us a copy that I do know works. We are still using it for our finance loads. I really can not answer whether they should be commented out or not. We have some loads going today and I will not be able to test uncommenting the lines. I will try some time next week.

Hi Guru

what is the session have the same function with tuxch9223m000 session?

Regards,

Thang

is there anyone know how to import excel data in BAAN JVR (Ver IV).....?????

This is not the correct thread, but the process is simple one to me. Save the excel file as comma delimited, read the file in, and then use stpapi commands to write the JV records. You can check out the AFS/DDC/OLE forum for information on the stpapi commands.