Prefilling Line with Data below (not from previous line)

Hello:

I am using Monarch Classic 13. I'm a bit of a novice with Monarch, so I apologize if this is obvious for the gurus out there. I have a report (see below) where the member name is missing from some of the lines. Normally I would use the option to fill from the previous line, but in this instance, the report was designed an opposite manner. I need to bring the name up from the "next" line. What you see below is page 1 of the report. So, the 1st record doesn't have the name. I need to bring the name up from the 3rd row, and use it to populate the missing name in rows 1 & 2. Then I need to bring the name up from row 5 to populate the missing name in row 4, etc.

Here's the other component. The report "logic" starts over after every page break (noted by the dotted line). For instance, in the screen shot below. The record ending in 708 (above the dotted line) belongs to the record right above it. Below the dotted line, I need to bring the name in the 3rd record up and fill the two records above it. Hope that makes sense.

I do have a solution that involves concatenating the contract ID, Mbr, and Member Name fields, and then exporting them into a separate table to create a reference list, and then bring back in as a lookup, but I'm hoping to avoid that if possible. I'm hoping someone else has encountered this before and knows of a way to handle this using traps within the existing model.

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

I would set my detail line as you have it, capturing the name even if it doesn't exist on the row. I would then create a footer template that would capture the lines that just contain the name. This allows that field to populate up. You would then need to create a formula field to compare the detail name to the footer name. If the detail name is blank, then use the footer field name, otherwise use the detail name. I will try to update my response with screenshots and formulas later if you need more info.

In regards to the page break, if each page is separated by "Total members terminated....", you might try trapping that as a page header (even though it is at the bottom of the page). Page header templates have some special properties that allow fields to span two pages. I haven't set up anything to test your scenario so it might not work, but worth checking out.

Altair Forum User

I would set my detail line as you have it, capturing the name even if it doesn't exist on the row. I would then create a footer template that would capture the lines that just contain the name. This allows that field to populate up. You would then need to create a formula field to compare the detail name to the footer name. If the detail name is blank, then use the footer field name, otherwise use the detail name. I will try to update my response with screenshots and formulas later if you need more info.

In regards to the page break, if each page is separated by "Total members terminated....", you might try trapping that as a page header (even though it is at the bottom of the page). Page header templates have some special properties that allow fields to span two pages. I haven't set up anything to test your scenario so it might not work, but worth checking out.

Chris - thank you for the reply. I'm going to give it a try now, and let you know if I succeed.

Altair Forum User

I would set my detail line as you have it, capturing the name even if it doesn't exist on the row. I would then create a footer template that would capture the lines that just contain the name. This allows that field to populate up. You would then need to create a formula field to compare the detail name to the footer name. If the detail name is blank, then use the footer field name, otherwise use the detail name. I will try to update my response with screenshots and formulas later if you need more info.

In regards to the page break, if each page is separated by "Total members terminated....", you might try trapping that as a page header (even though it is at the bottom of the page). Page header templates have some special properties that allow fields to span two pages. I haven't set up anything to test your scenario so it might not work, but worth checking out.

Chris - your suggestion works perfectly for my scenario. Thank you so much!!

Altair Forum User

Chris - your suggestion works perfectly for my scenario. Thank you so much!!

Bill,

Your original approach to create a lookup table as a first pass of a 2 pass approach is very a very solid solution that is applicable broadly across this scenario and near but not quite exact examples.

Chris's solution is very elegant and works well in this example.

The third possible approach that people might find handy if the data does not play nicely in this type of problem would be to extract all of the lines just as they are as a single field per line.

Work out a sort field (probably a calculated field) for the data and invert the order of the fields in the table with the sort.

Output a revised version of the resorted report to a new report.

You now have a report for which you can use the "Fill from previous line" option. At also uses pretty much the same extraction model since the data structure across the row is the same.

All the approaches have merit in this situation and which works best for the occasion probably comes down to some nuance of data presentation in the specific report that requires analysis.

Grant

Altair Forum User

Bill,

Your original approach to create a lookup table as a first pass of a 2 pass approach is very a very solid solution that is applicable broadly across this scenario and near but not quite exact examples.

Chris's solution is very elegant and works well in this example.

The third possible approach that people might find handy if the data does not play nicely in this type of problem would be to extract all of the lines just as they are as a single field per line.

Work out a sort field (probably a calculated field) for the data and invert the order of the fields in the table with the sort.

Output a revised version of the resorted report to a new report.

You now have a report for which you can use the "Fill from previous line" option. At also uses pretty much the same extraction model since the data structure across the row is the same.

All the approaches have merit in this situation and which works best for the occasion probably comes down to some nuance of data presentation in the specific report that requires analysis.

Grant

Thanks for the additional suggestions, Grant. I'll definitely keep those in mind for future scenarios.