Split dates from string with } delimited into individual fields

[cpjeffm]

Ok I'm stuck on this one and need some help. I've got a WOSummary.DepositPostedDate string field. This field shows dates deposits were taken on an order separated by a }. The field looks like this...

05/08/2017 or 05/08/2017}05/09/2017 or 05/08/2017}05/09/2017}05/10/2017 and so on depending on how many deposits were taken on the order.

I need a formula that will split these into separate fields. So if there's only a single date, return only that date. If there's two dates separated by a }, I need one field to show the first date and one field to show the second date. If there's three dates, I need one field to show the first date, one to show the second, and one to show the third, etc.

There can be any number of dates in this field but I don't think anyone would take more than 5 deposits on a single order.

 

[kray4660]

I would think you would need to use the split function and store it in a array. Below is an one example on how that might be done.

stringvar array result := Split ('05/08/2017}05/09/2017}05/10/2017','}' );
numbervar i;
stringvar out;
for i := 1 to count(result) do
(
out := out+result + chr(10)
);
out

 

[LaurieHamlin]

So the field only has more than 1 date if the length is > 10. If the dates are always entered in that format (2 digit month, 2 digit day, 4 digit year), you can just use Left({DepositPostedDate}, 10) and Mid({DepositPostedDate}, 12, 10) and Right({DepositPostedDate}, 10). If not you'd have to use InStr to find the }, then take the left, mid and right.
I hope that helps.

 

[pmax9999]

If you want every date returned in a separate formula, you will need to do it this way:

1st Date:
If UBound(Split({Table.Field}, '}')) >= 1
Then Split({Table.Field}, '}')[1]

2nd Date
If UBound(Split({Table.Field}, '}')) >= 2
Then Split({Table.Field}, '}')[2]

3rd Date
If UBound(Split({Table.Field}, '}')) >= 3
Then Split({Table.Field}, '}')[3]

... and so on.


If you want them all in a single formula, one under the other do it this way:

WhilePrintingRecords;
Local StringVar RESULT;
Local Numbervar i;


For i := 1 to UBound(Split({Table.Field}, '}'))
Do  RESULT := RESULT + Split({Table.Field}, '}')[i] + CHR(10);

RESULT

Hope this helps.

Cheers
Pete