We are running into an issue with long form dates extending across multiple lines (see attached example). What is the best method to capture data that falls this way in the report model?
Would a formula field be required or is there a simple solution for these data instances?
If we assume that the date is always present at the end of the line and is covering more than one line, we can extract it in the following manner.
- After Creating the data region, we need to capture both the lines completely that comprise of date at the end.
In the Field Properties Window for the Data field, give the length field as "Till end of Line" and Height as "Till Region Ends".
After this you will need to create a formula field and give the expression as mentioned in the screenshot attached below.
In this manner, the desired date will be captured from the given file.
One follow-up question regarding the formula field, is there a way to make that more universal, or would it need to be set to look for a specific string like it was in this example for the specific date format?
For example, if this weren’t a date field, and we were trying to capture an account name across two lines, and the field varied in pattern/length. A date is more of a fixed formula, whereas an account name could be any number of characters in length and different formatting between characters/spaces.
I have attached a scrubbed statement that shows the data condition. We are trying to grab the account name which in the attached is “Name X. Name 2018 Family Trust.”
If we assume that due and from is present in all the files then we can extract the account name even if it varies in size/ length.
Following are the steps through which you can capture date and account name :
- Capture both the lines covering the data in a single data field.
- Once the data field is created, go to the field properties window and check ’ Remove Extra Spaces from Text’ along with that select the Length as ’ Till end of line ’ and Height as ’ Till region ends’ .
- Then you are required to create a formula field that will capture the data after the string ’ due '.
- After this, you need to split the string using the delimiter as ’from’ using the LSplit function.
Doing so, you can extract the date and Account name details present in the file.
I am also attaching the screenshot of a sample report model for your guidance. Do let us know if this solution helps in solving your concern.
