Multiple records from same field on one line... kinda 1

[jenfromjax]

I apologize if this has been addressed somewhere in this forum, but I haven't had luck searching keywords or FAQs.

I have been asked to create a report that will show all our clients & which of our products they subscribe to. And if they do not use our product for a particular category, then print the name of the 3rd party vendor they use. I was asked to format this like an Excel spreadsheet, all info confined to one line per client. All of the client names are in one field in Table-A, all of the possible products are in one field in Table-B, the 3rd party info (if any) are in another field in Table-B. They want ALL clients to appear, regardless of the number of products they use (left outter join?). Here's an idea of how they want to it magically appear:

Name Software Hardware Other
ABC,Inc "Us" "Them (Null-no info on file)
XYZ,Inc "Them" (Null) "Us"

Or

Name Software Hardware Other
Cust.Name regir.prod_id regir.prod_id regir.prod_id

I created a formula that I hoped would work for the product columns (3rd party vendor = regir.field1)

If {regir.prod_id} = "TL" then "Us" else
If {regir.prod_id} = "TELLER" then {regir.field1}

I get a lot of duplicate rows, clients who don't show up because they don't have the product named in the formula, etc. I have tried different joins, conditional formatting to fix the dupe problem, alias tables, select criteria, just about anything you can imagine.

I know this probably isn't very clear... I have been starring at this God foresaken report for weeks so I am not thinking straight right now. Please let me know if you have any ideas or need additional info.

Thank you!!!
Jen

 

[synapsevampire]

A Left Outer is correct to assure the right rows.

This must be wrong:

Name Software Hardware Other
Cust.Name regir.prod_id regir.prod_id regir.prod_id

The Other is regir.field1, correct?

How is category defined???

Share the correct field names and how you differentiate software and hardware, I'm sure this is doable.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jenfromjax]

You're right... I am really over simplifying it in my initial post. I was using that formula ("Teller&quot in place of the prod_id field in the software column, a similar formula in the hardware column, and so on. What ended up happening was it still printed a row for each individual result in the columns, meaning - if a client had a value for all three columns in the example, it returned 3 rows for one client.

Right now it looks exactly like this:
Name: Software1: Software2:
cust.name @Teller @Account

@Teller formula=
If {nxsregir.prod_id} = "TL" then "Our Name" else
If {nxsregir.prod_id} = "TELLER" then {nxsregir.field1}

@Account formula=
If {nxsregir.prod_id} = "AP1" then "Our Name" else
If {nxsregir.prod_id} = "OR" then "Our Name" else
If {nxsregir.prod_id} = "AO" then {nxsregir.field1}

They are about 15 different types of Software columns that will need to be on the report when I am finished, but there are just these 2 right now until I get this figured out.

Did this help at all or just confuse it? Thanks for taking time to help.

~Jen

 

[synapsevampire]

Please don't do anything in the record selection formula which references anything in Table B (child).

This seems OK, (it may return "&quot:
@Teller formula=
If {nxsregir.prod_id} = "TL" then "Our Name" else
If {nxsregir.prod_id} = "TELLER" then {nxsregir.field1}
else
""

Simplified this (it may return "&quot:
@Account formula=
If {nxsregir.prod_id} in ["AP1","OR"] then "Our Name" else
If {nxsregir.prod_id} = "AO" then {nxsregir.field1}
else
""

The Left Outer should will resolve clients NOT showing up (providing you don't add record selection on table B), so the only thing left on your problem list is multiple rows.

First group by the Name column.

With the addition of the else "" to these formulas, you now have the ability to group on a formula which is a combination of these 2 formuals, such as:

@GroupFormula
@Teller+@account

Now you can place your fields in the group footer, suppressing the details and group header, and a conditional suppression on the group footer where @GroupFormula = ""

I'm not sure if this is what you consider a dupe, but this would only show ONE of each type of @GroupFormula.

Hope this helps.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jenfromjax]

OK, I don't want to jinx it (it IS Friday the 13th), but I think this works!! It seems too good to be true. You have no idea what a big deal this is. If you were here I'd kiss you.

I have a question though - why can't you put anything from the child table in the select criteria? I had noticed that before, but wasn't sure why that occured.

Also, why are the fields in the Group footer instead of Details?

Thanks again. I think you just saved my job.

~Jen

 

[jenfromjax]

I think I did speak too soon. (
I have run across another issue = The introduction of Table-C. How would this table be joined to the others? Another Left Outter or from Table-B on an equal join.
I need the report to show each client's "Core Vendor" which is {nxsdatas.field1}. However, this field is used in our database for other information not related to Core vendor. In any other report I would add select criteria as follows:
{nxsdatas.type_code} = "VENDOR"
But, from your previous post you said not to add any such criteria from a child table. So I created the following formula & added it to the Group Footer with the others:
If {nxsdatas.type_code} in ["VENDOR"] then {nxsdatas.field1} else ""

or

If {nxsdatas.type_code} = "VENDOR" then {nxsdatas.field1} else ""

Neither worked. Any ideas?

 

[synapsevampire]

Table C is a new wrinkle here...

Depending upon where you join the table, and it's purpose.

I think if you join Table C to Table A and add in the record selection you'll be fine, providing you ONLY want Table A rows that have this match in Table C.

Adding a criteria to Table B will state that only rows which match this criteria should be returned, even for Table A

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jenfromjax]

Well, I think I followed that...
What I am trying to accomplish is something like this:
Table A - All
Table B - if any
Table C - if any

The way I understood your post was that only the records in Table A that have a value in Table C will appear (?)

Is that the case? If so, is there a way around that?

Thanks again!
~Jen

 

[jenfromjax]

Also...
It is understood that if there is no Core Vendor info contained in {datas.field1} that we are the Core Vendor.

Is there a way to write it so that -
If datas.field1 ({nxsdatas.type_code} = "VENDOR&quot is null then "our name" else datas.field1
?

If it matters, I am using Crystal 8.5

 

[jenfromjax]

Update (if you are still with me)...

Somehow I have managed to get the Core Vendor to work & the Teller column returning accurate records, all on the same line (in Group Footer). However, any column following Teller does not return any records (on the same line in the Groop Footer). Once I moved my fields back up to detail it gave me one row for Teller & one row for Account. So I guess I am back to the original issue of not being able to successfully report everything on one line. HELP!!!

Name Core Teller Account GUI COLD
ABC,Inc Us Them (nothing) (nothing) (nothing)
XYZ,Inc Them Us (nothing) (nothing) (nothing)

 

[synapsevampire]

Don't use the record selection criteria unless you mean to limit the rows.

By stating that it's "VENDOR", you do so.

Also, try ordering by the other columns.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jenfromjax]

I did try ordering by the other columns. Whichever field it is sorted by first (between the Teller, Account & etc) that first field is the only one that shows up in the Group Footer.

I don't understand why it will not return results for more than one formula utilizing one common field.