Blank dates in source file
D Doane asked 6 years ago
We have built a map to import SOP trxs, some with multiple line items. As part of this, using the Add User Defined node, there are 2 User Defined Dates we want to attach to the SOP entry. We are grouping this node by the Document Number.
We have two types of line items which could be on a single document where 1 has both of these dates and the other does not. To bring these dates in at the SOP level, we’ve set it to bring in the first fields’ date as the minimum and the second date as maximum. For those records with a mismatch of dates, we’re wanting the broadest range the lines may have. The maximum works fine for the second date but the minimum captures the blank date (00/00/0000) rather than the line record with a valid date.
We’ve tried to set a restriction on the node to bypass those records with blank dates hoping it would then only evaluate the actual dates on the remaining line item records for the document. We still get 00/00/0000 as the date in the first field.
Have also attempted to set the first date field as a calculation where if the date is blank, get current date and add 5 years (this is way past any max date in the second field), but we still get 00/00/0000 in the first date field.
Does anyone have other suggestions on how we could do this? Thank you
We have two types of line items which could be on a single document where 1 has both of these dates and the other does not. To bring these dates in at the SOP level, we’ve set it to bring in the first fields’ date as the minimum and the second date as maximum. For those records with a mismatch of dates, we’re wanting the broadest range the lines may have. The maximum works fine for the second date but the minimum captures the blank date (00/00/0000) rather than the line record with a valid date.
We’ve tried to set a restriction on the node to bypass those records with blank dates hoping it would then only evaluate the actual dates on the remaining line item records for the document. We still get 00/00/0000 as the date in the first field.
Have also attempted to set the first date field as a calculation where if the date is blank, get current date and add 5 years (this is way past any max date in the second field), but we still get 00/00/0000 in the first date field.
Does anyone have other suggestions on how we could do this? Thank you
Answers
Patrick Roth Staff answered 6 years ago
,
You can use a restriction because the Restriction is only used as SC processes the data. That’s why that doesn’t help.
a Blank date of 00/00/0000 IS a valid minimum date – that is why it is being selected as MIN – it’s just that you want to select the MIN() date where date is <> “00/00/0000”.
For this mapping, I don’t see that you’ll be able to get the Min/Max values with this because it is being grouped. That ties us to what the min() gets us which is the 00/00/0000 that you don’t want.
So what you would do is check this yourself in a node that processes all the data and find the min yourself.
so on a SOP map, you have the ‘add line item’ mapping that you use to create the line items – this is the one that you’ll add code to.
Lorren does something similar to this technique in this article:
http://www.eonesolutions.com/blog-post/tech-tuesday-sum-document-line-amounts-using-global-variables/
however he is summing a total vs finding a “min” but the idea is essentially the same.
so you mention: “We have two types of line items which could be on a single document where 1 has both of these dates and the other does not”
In the Restrictions script of that line item mapping, you would evaluate your line item type and then the date.
ADD LINE ITEM RESTRICTION SCRIPT
if _MYDATE.tostring(“yyyy-MM-dd”) <> “0000-00-00” and ( _MYDATE < GBL_MINDATE) then
GBL_MINDATE = _MYDATE
end if
so here we assume you made a global called GBL_MINDATE and your date field in the source is _MYDATE.
So as smartconnect processes the line items, it checks the _MYDATE field and sees if it isn’t the zero date. If it isn’t zero date, then it checks to see if the date is less than the current date that we’ve found. If it is, then it sets our variable to that new “min” value we just found.
If you wanted, you could do the same for the “max” date as well by making a new check with _MYDATE > GBL_MAXDATE.
but since max() function will get that for you you shouldn’t have to bother.
Now that we will have our real min date in GBL_MINDATE, you just need another calculation to return it.
dim mindate as date
mindate = GBL_MINDATE
GBL_MINDATE = “00/00/0000”
return mindate
Then map this calculation in your user defined mapping on the map.
Note that I didn’t test the above code and isn’t error trapping/checking too much to make sure it can’t break. so you’ll want to test it under a few different situations.
But the base theory is the only way you’ll get this to work.
patrick
You can use a restriction because the Restriction is only used as SC processes the data. That’s why that doesn’t help.
a Blank date of 00/00/0000 IS a valid minimum date – that is why it is being selected as MIN – it’s just that you want to select the MIN() date where date is <> “00/00/0000”.
For this mapping, I don’t see that you’ll be able to get the Min/Max values with this because it is being grouped. That ties us to what the min() gets us which is the 00/00/0000 that you don’t want.
So what you would do is check this yourself in a node that processes all the data and find the min yourself.
so on a SOP map, you have the ‘add line item’ mapping that you use to create the line items – this is the one that you’ll add code to.
Lorren does something similar to this technique in this article:
http://www.eonesolutions.com/blog-post/tech-tuesday-sum-document-line-amounts-using-global-variables/
however he is summing a total vs finding a “min” but the idea is essentially the same.
so you mention: “We have two types of line items which could be on a single document where 1 has both of these dates and the other does not”
In the Restrictions script of that line item mapping, you would evaluate your line item type and then the date.
ADD LINE ITEM RESTRICTION SCRIPT
if _MYDATE.tostring(“yyyy-MM-dd”) <> “0000-00-00” and ( _MYDATE < GBL_MINDATE) then
GBL_MINDATE = _MYDATE
end if
so here we assume you made a global called GBL_MINDATE and your date field in the source is _MYDATE.
So as smartconnect processes the line items, it checks the _MYDATE field and sees if it isn’t the zero date. If it isn’t zero date, then it checks to see if the date is less than the current date that we’ve found. If it is, then it sets our variable to that new “min” value we just found.
If you wanted, you could do the same for the “max” date as well by making a new check with _MYDATE > GBL_MAXDATE.
but since max() function will get that for you you shouldn’t have to bother.
Now that we will have our real min date in GBL_MINDATE, you just need another calculation to return it.
dim mindate as date
mindate = GBL_MINDATE
GBL_MINDATE = “00/00/0000”
return mindate
Then map this calculation in your user defined mapping on the map.
Note that I didn’t test the above code and isn’t error trapping/checking too much to make sure it can’t break. so you’ll want to test it under a few different situations.
But the base theory is the only way you’ll get this to work.
patrick