Capturing multiple tables of the same type of data

[rdaneel72]

Is this going to work?

Instead of trying to map all detail line in a credit union statement into 1 table, then worrying about styling each transaction type later, I had the genius idea of capturing each set of transactions into a unique table. But I can’t figure out how to capture a second table of the same type.

Our data has a code at the beginning of each line that we used in PP7Talk to style the line with fonts and, more importantly, position of each element. Essentially making tables. But using a Repeat tool to map a table based on this code (While Condition is True) only creates a table for the first instance of each code.

Is this even possible? Or should I just capture all detail lines into 1 table, then use JavaScript to style and position the elements on each row of the table? Is THAT even possible?

Which approach should I pursue?

[jchamel]

Instead of using multiple Repeat as you did, do 1 Repeat and in it a multiple condition statement like this:

Each condition check the beginning of the line an then the extraction is done accordingly. Each section of the multiple statement can become its own detail table.

[rdaneel72]

Wow, that’s cool.

Thanks for the idea. I will explore this further.

I didn’t even realize something like this is possible.

[jchamel]

Huuummm…learing you are, my Padawan

[rdaneel72]

Unfortunately, I don’t think this is going to work for me either.

You can see in the data there are 2 groups of detail lines with the same flag “SUM” and a separate group “TRN” in between. The data is structured like this throughout, with the same flags for different accounts all mixed together on every page.

This method is extracting both sets of lines flagged “SUM” into the same detail table, but they need to be separated in the template, with the “TRN” group in between. Basically, the detail lines must remain in the same order they appear in the data. I am realizing that once DataMapper extracts the data, the original data structure is just gone. I don’t know how to reconcile this.

To retain the structure and order of the detail lines for this statement, I am going to need to extract all lines into a single table, and then structure them all differently.

How am I going to do that? I am going to have to hand this project over to an expert at OL.

[jchamel]

Then setup another extraction inside the SUM extraction. Most likely your groups of SUM always start with a specific key that shows it is the 1st line of the group.

You’d then have record.sums.sum.

You’ll need ot keep track of which sums.sum you are looking at but it is feasible.
You could use a Property in which you would keep what is the previous line prefix value.

From your screenshot, it looks like your SUM groups start with SUMH. You logic would check if the previous line started with a SUMH. If not then it is a new record.SUMS.SUM subrecord.

But if the order need to be the same, then have a single detail table with 1 field that tells you which “group” it is. Then in it a nested detail table for the subgroup.

[rdaneel72]

Nested detail tables?

Like, I could theoretically wrap all these multiple condition extractions into 1 extraction loop that would first capture the whole transaction line (in order), then execute each individual conditional extraction?

Every time I hit a wall, you hand me a hammer and chisel

[jchamel]

You are mixing both my solutions…

Setup a single repeat with a detail table and a nested table

• Repeat for all lines
• Check if the prefix on the lines is different than the one stored in the Property
• If it is, extract the prefix in an extraction Record.Group, field name group
• Extract the other values on the line in a different Extraction, Data Table field Record.Group.Values, and all fields you need there
• If the content of Property differs from the prefix of the lines, update the Property with the new prefix

This way you have Record.Group (whatever fields you put there) to kow which group you are in.
Record.Group.Values for the values.

[rdaneel72]

Getting closer, but I am not capturing the first line when the property changes.

I am also unclear in how to capture the necessary fields for each transaction type. Will I use the Multiple Conditions to create different detail tables for each transaction type (prefix)? Each line has different fields.

[jchamel]

If you want to keep the order of the lines as they are at the moment, meaning when a group repeat itself multiple times throughout the same record, having all of them on the same detail table is the way to go.

Please share in private what you have so farso I can see what would be the best way.

[rdaneel72]

Thank you for offering to look at this. I have sent you a link in a private message.

My initial attempt was to create 1 detail table with 1 field, capturing the entire line. I figured I would need to use JavaScript to style and format each line of the table, splitting certain lines into multiple columns to recreate the PP7 form. But I do not even know if that is possible with JS. I certainly don’t know enough about JS to do it.

Then I thought that if I create a detail table for each set of transactions, I could style and format them separately, and call them by the flag/code. Until I realized the order of the detail lines is lost in datamapper.

Your mention of nested tables got me thinking. A main detail table to preserve the order of the transaction lines, then call individual, formatted detail tables for each transaction type by the flag, with the correct styling and number of columns/fields.

We print and mail account statements for about 25 credit unions every month, and the data for all of them is structured like this. I have learned a lot on this forum over the last year, and this feels like the last piece I need to start converting over 150 PP7 jobs to Connect.

I really appreciate the attention provided by this forum.

[jchamel]

Posted a potential approach for you to complete in our private messages session.

[rdaneel72]

Wow, this is cool. Thanks so much for getting me started. I do not know much JavaScript, so this is not an approach I would ever think of.

I wonder how this will work on the template. Will it be 1 main table calling separate tables for each FLAG somehow? And if that is the case, then each table can have different numbers of columns (fields) than can be positioned. But since it is all 1 table, it will retain the order of the transaction lines in the data.

I feel like I have an idea on how to convert many of our other types of jobs from PP7 to Connect (utility bills, dozens of daily notices, direct mail), but the account statements were really worrying me. Thank you for showing me a path forward.

for posterity, here is @jchamel’s solution, extracting fields based on another field in the data to create dynamic, nested detail tables

var groupIndex = record.group.length-1;
switch(record.group[groupIndex].FLAG){
	case 'HDR':
		data.extract(13,151,0,1,"<br />");
		break;
	case 'SUM':
		data.extract(13,105,0,1,"<br />");
		break;
	case 'SAS':
		data.extract(13,25,0,1,"<br />");
		break;
	case 'LTR':
		data.extract(13,65,0,1,"<br />");
		break;
	default:
	    '';
}

Maybe this is simple and everyone but me already knows how to do this.

[jchamel]

For the Template, you can have multiple dynamic tables, each linked to the same detail table but conditionally reacting to the record.group.FLAG field and only displaying detail lines for those matching the flag.

For your Datamapper, as I provided to you, I would create more columns, as many as the maximum number of column possible for all flags.

Then you choose for each flag which one holds what information and in our dynamic tables, you select which column to display for each.

[rdaneel72]

Great. Thank you so much for the instruction. I look forward to proceeding.

[rdaneel72]

This is exciting. I am so close, but it looks like my main table is applying BOTH nested tables at the same time, and not advancing to the next line.

There must be something simple I am overlooking.

<table id="table.lines" data-column-resize="" data-hide-when-empty="" anchor="page_media_0"
style="width: 6.375in; position: absolute; top: 276px; left: 107.033px; height: 0.1666in;" data-expander="2019" offset-x="108" offset-y="420">
    <tbody>
        <tr data-repeat="group">
            <td id="{{{FLAG}}}" style="width: 99.35%;"></td>
        </tr>
    </tbody>
</table>


<table id="HDR" data-column-resize="" data-hide-when-empty="" anchor="page_media_0"
style="width: 6.375in; height: 0.1666in; position: absolute; top: 276px; left: 107.033px;" data-expander="2019" offset-x="108" offset-y="420">
    <tbody>
        <tr data-repeat="group.values">
            <td class="HDRH" style="width: 99.35%;">{{{column3}}}</td> 
        </tr>
    </tbody>
</table>


<table id="PLC" data-column-resize="" data-hide-when-empty="" anchor="page_media_0"
style="width: 6.375in; height: 0.1666in; position: absolute; top: 276px; left: 107.033px;" data-expander="2019" offset-x="108" offset-y="420">
    <tbody>
        <tr data-repeat="group.values">
            <td class="PLC" style="width: 99.35%;">{{{column3}}}</td> 
        </tr>
    </tbody>
</table>

Can anyone see what I am missing?

[jchamel]

You are absolutely positionning your tables. Therefore they cannot follow the current of the flow.

In layman terms, if a table gets bigger it overlaps on the others because the other have an absolute position on the page.

[jchamel]

You can read Relative vs Absolute vs Fixed Position in CSS - GeeksforGeeks

[rdaneel72]

Yes, after posting I changed the position of the sub tables to “relative”, so now the second table follows the first, on another page.

Every detail line is being printed by both tables. The HDR table prints every line in the bold font with the border, and after that the PLC table prints every detail line again. I am not sure if the issue is with the main table or the nested tables.

The tables aren’t really nested. The main table is complete and then I have the sub-tables below. Is that the problem? Should all the sub-tables be nested within the main table?

Not sure how to fix this.

[jchamel]

I was thinking something more like this:

<table style="width :100%" data-column-resize="" data-hide-when-empty="" data-expander="2019" id="table1">
	<thead>
		<tr>
			<th style="text-align: left; width: 30.49%;">Code</th>
			<th style="text-align: left; width: 28.76%;">Date</th>
			<th style="text-align: left; width: 40.31%;">column3</th>
		</tr>
	</thead>
	<tbody>
		<tr data-repeat="group">
			<td style="text-align: left;" colspan="3">The flag is <b>{{{FLAG}}}</b></td>
		</tr>
		<tr data-repeat="group.values" class={{{FLAG}}}>
			<td style="text-align: left;" class={{{Code}}}>{{{Code}}}</td>
			<td style="text-align: left;" class={{{Date}}}>{{{Date}}}</td>
			<td style="text-align: left;" class={{{column3}}}>{{{column3}}}</td>
		</tr>
	</tbody>
</table>

In the class {{{FLAG}}} (PLC, HDR, etc…) you set up what is common to the columns. Then if you need specific for column, you can set CSS for Date, column3, Code, etc…

This way you don’t need multiple tables and you keep the order.