CR 2008: Re Group 1

[npp83]

Hey guys I have a problem but first, let me issue a few bits of background information to help you understand my report a little better.

A preamble to my problem:

I have two groups: G1)Branch G2) LoanOfficer. Both of these were created using Database fields and are not formula fields. Loan officers are associated with Loans and Loans are associated with branches based on their LoanNumber. An example LoanNumber would be MUR120002329. To "join" the loannumber to a particular branch, in the Select Record Expert, I have written:

Left({LoanSummary.LoanNumber}, 3) = {org_chart.org_code}

org_code is a three letter indicator for the branch.

In the report I am working on it has been the norm that branches are separate entitities and will display information on the loans each loan officer is associated with. For example, Murfreesboro loans stay with Murfressboro and Brentwood loans stay with Brentwood. But not that has changed and I have been given instructions to merge loans from on branch with another so that MUR loans appear under Brentwood Branch. Question: Is there anyway to spoof a join so that:
(psuedo code) if Left({LoanSummary.LoanNumber}, 3) = "MUR" then "BRE" so I can see loan officers and loans from MUR under the Brentwood branch? Thanks.

 

[lbass]

So you are expecting a loan to appear in two different groups? I.e., the MUR loan would also appear under the BRE group?

-LB

 

[npp83]

As per my instructions, the loans from MUR must appear only in BRR (Brentwood). I personally don't know if this is possible to do with 1) dynamically pulled data straight from the DB Fields 2) even with reassignment in a formula field.

 

[lbass]

Assuming this is the case, use your formula (let's all it {@left3}) to associate the loan with the branch group. Then you could insert the loan table fields in a subreport that uses a second formula {@mid4} as the linking field from the main report:

mid({LoanSummary.LoanNumber},4)

...linked to the same formula in the subreport.

Place the sub in a group section for the branch.

-LB

 

[lbass]

Missed your post. In that case, I think you should just use a formula like this:

replace({LoanSummary.LoanNumber},"MUR","BRE")

Then use the first three characters of this formula to link to the org code.

-LB

 

[npp83]

When using the "replace" should I append
replace({LoanSummary.LoanNumber},"MUR","BRE") in my Select Record statement? For example:

{LoanSummary.CurrentMilestoneName} in ["Completion", "Funding", "Post Closing", "Shipping", "Sold"] and
{LoanSummary.LoanFolder} like ["Closed Loans", "My Pipeline"] and
{org_chart.parent} = 51 and
{org_chart.org_name} like {?Branch} and
Left({LoanSummary.LoanNumber}, 3) = {org_chart.org_code} and
(({LOANXDB_S_06._2626} = "Correspondent" and {LOANXDB_D_02._2553} = {?FundedDates}) or
({LOANXDB_S_06._2626} = "Brokered" and not(isnull({LOANXDB_D_02._CX_DT_PAYROLL_BROKER}))
and {LOANXDB_D_02._CX_DT_PAYROLL_BROKER} = {?FundedDates}))
and replace({LoanSummary.LoanNumber},"MUR","BRE")

 

[lbass]

change this line:

Left({LoanSummary.LoanNumber}, 3) = {org_chart.org_code} and


to:

left({@replaceformula},3) = {org_chart.org_code}

-LB

 

[npp83]

Two Questions about that:

1. Does the custom formula field (@Replace) then only contain replace({LoanSummary.LoanNumber},"MUR","BRE")

2. What then would indicate the default behavior where if a loan does not equal MUR (a loan from another branch) use the standard Left({LoanSummary.LoanNumber}, 3) = {org_chart.org_code} to determine the branch?

Thank your exerted effort on my problem.

 

[npp83]

Ah hah! I got it. Nevermind about that previous question. Thank you.