• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Validate inbound date format in EDI CSV to F47 table

johndanter

Legendary Poster
Hi List,

I have a weird one I can't think how to solve.

A User drops a CSV file, EBI software picks this data up and maps the data into an F47 table. I've then written a UBE to read the inbound data and check various things before entry into E1.

One of the things I've been asked to check is the Effective Date (F41061.CBEFTJ) is in the correct format. YYYMMDD.

So how do I do that? As EBI will map a value into this into my F47 F41061 equivalent table and then my code will have to check it somehow..............but how????

How do you check date is 'valid' in E1?

Could I maybe split the parts out using B0000078 and then ask is the days between 1 - 31 and months 1 - 12? That's all I can think of.
Or...I was thinking of adding a new string column to my F47 table (it's F57 really) and then validate it from there. But what BSFN checks YYYYMMDD

EDIT: This works actually
Convert String to Date Using Format Mask B74I0010 YYYYMMDD
Converted Date < 31/01/74
StringToConvert > 19740131
FormatMask > EOA


Thanks

John
 
Last edited:

craig_welton

Legendary Poster
John,

This is confusing, is it a 47 or 57 or F41061 table EBI is writing to? are they JDEDate data types?

Formatting a JDEDate as YYYMMDD will always work as long as the underlying number is a valid JDE Julian Date. If you're questioning whether EBI will populate a valid JDE Julian Date number (since 1900 years, days in year), manually load a bad date (100500 for example) and see how JDE evaluates it in code. Perhaps it's a NULL Date? You're a little handcuffed within the toolset since any references to a JDEDate column will do the JDE Julian Date number to date conversion for you, i.e. you never see the underlying data.

Craig
 

johndanter

Legendary Poster
Yeah sorry Craig, badly written :)

The inbound CSV gets dumped into my F5741061 table (F47 style table) using EBI. Then I read the F5741061 and then have to report if the date is invalid. It's format or if it's blank. If all is OK, I then insert it to F41061.

I've sorted this by getting the EBI mapping software to dump the YYYMMDD date into a new 10 char field. I can then use B0000078 to get the date which I then place in EFTJ myself. If it's invalid, the output date is blank coming back from B0000078

Although in the future I would like to know how to work out if a date in a inbound date column has an invalid value. As you are right about the Julian and NULL date issue.

Issue I had is EBIs validation will not write the entire inbound line if it detects a bad date. Which is not what I wanted as I need the inbound data and also need E1 to report on the bad date, not EBI. Problems problems :)
 
Top