Skip to Content
author's profile photo Former Member
Former Member

HELP!! Obtaining correct data within field

Post Author: MWinn

CA Forum: General

I have to create a report that will be sent to our customers at the end of each month. We are in the ATM servicing industry. We just installed a program for our Call Center, Inventory, and accounting. The data bases consist of the normal tables and fields, however, the fields with in the tables contain overlapping information. I am having trouble getting the correct information form individual fields. For instance I need to pull the problem description from a table called SBCODE using a field called SCDDS2 (SBCODE.SCDDS2), however, there is a lot of other information in that Field that has nothing to do with the problem code. The programmers used a field called SCDTYP (SBCODE.SCDTYP) to enter a code that they would then use to define what information is stored there. I.E. "PR" would represent the problem description part of the field. I can use the selection expert and define that SCDTYP = "PR" and get the correct results for the Problem Description, however, when I have to use that same table (SBCODE) to get the Technician Description I would then have to set SBCODE.SCTYP to "TE" which would then give bogus results for the problem code. I tried to create a formula for each of the different fields that I need to insert into the report, but to no avail. Basically I need a way to define SBCODE.SCDTYP for a number of different fields (SBCODE.SCDDS2 and SBCODE.SCDTNM) that we need to use in order to pull the correct information and I am unable to figure out just how to do this.

Any suggestions would be appreciated. I have already linked the tables with corresponding fields. I am kinda new to whole formula thing but I tried to just create a formula that difined SBCODE.SCDTYP = "PR"; SBCODE.SCDDS2 and then insert that formula into the report as a field entry, but it didn't work.

Thanks,

Michael D. Winn

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2007 at 07:36 PM

    Post Author: V361

    CA Forum: General

    Hi Mike, can you include some samples, also, you may want to look at a subreport as a possible solution, you can pass values from the main report to the sub. So, once you have the SBCODE you can pull one SCDTYP in the main report and another SCDTYP in the sub.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 29, 2007 at 05:46 PM

    Post Author: MWinn

    CA Forum: General

    Sorry it took so long to get back to you, I just returned from vacation. I have not ever used a sub report before, how does that look when it is printed out and would I have to make more than one sub report if I need to define the SBCODE.SCDTYP in 4 different locations. I can actually send you the report if that would help but you wouldn't be able to see any of the actual data in it. Here is a sample of the data, I imported it into Excel and edited it so I can put it in here.

    SCDCMP

    SCDTYP

    SCDCOD

    SCDDS2

    SCDTNM

    01

    NO

    KVAL1959 01

    0038 SERVICES SELECTED

    0038 SERVICES SELECTED

    01

    NO

    KVAL1959 01

    0038 SERVICES SELECTED

    0038 SERVICES SELECTED

    01

    NO

    KVAL1959 01

    0038 SERVICES SELECTED

    0038 SERVICES SELECTED

    01

    NO

    KVAL1959 01

    0038 SERVICES SELECTED

    0038 SERVICES SELECTED

    01

    NO

    KVAL1959 01

    0038 SERVICES SELECTED

    0038 SERVICES SELECTED

    01

    OT

    VMS101

    VMS ORIGINAL TICKET SCREE

    VMS ORIGINAL TICKET SCREE

    01

    OT

    VMS102

    010203 0904050607

    010203 0904050607

    01

    OT

    VMS103

    08 [CONT

    08 [CONT

    01

    OT

    VMS104

    01

    OT

    VMS105

    01

    PR

    0003

    DEVICE IS CLOSED

    DEVICE IS CLOSED

    01

    PR

    0004

    DEVICE IS NOT LOADED

    DEVICE IS NOT LOADED

    01

    PR

    0005

    DEVICE IS IN SETTLEMENT

    DEVICE IS IN SETTLEMENT

    01

    PR

    0007

    DEVICE IN MAINTENANCE

    DEVICE IN MAINTENANCE

    01

    PR

    0102

    ATM RESET

    ATM RESET

    01

    PR

    0103

    NO ACTIVITY

    NO ACTIVITY

    01

    TE

    0001

    BRENT PEABODY

    BRENT PEABODY

    01

    TE

    0001 02

    0000000000000000000000000

    0000000000000000000000000

    01

    TE

    0001 03

    01

    TE

    0013

    DAVID WINDHAM

    DAVID WINDHAM

    01

    TE

    0013 02

    0000000000000000000000000

    0000000000000000000000000

    01

    TE

    0013 03

    01

    UA

    03901101 998910 20

    03901101 998910 20

    01

    WD

    01

    WD

    0003

    POWER FAIL - RESET ATM

    POWER FAIL - RESET ATM

    01

    WD

    0004

    RELOAD ATM SOFTWARE

    RELOAD ATM SOFTWARE

    01

    WD

    0005

    REMOVE FROM SETTLEMENT

    REMOVE FROM SETTLEMENT

    01

    WD

    0102

    RESET ATM

    RESET ATM

    01

    WD

    0103

    NO ACTIVITY IN SERVICE ON

    NO ACTIVITY IN SERVICE ON

    01

    WD

    0104

    RESET ALARM

    RESET ALARM

    01

    WD

    0105

    VERIFY POWER OUTAGE

    VERIFY POWER OUTAGE

    01

    WD

    0106

    RESET POWER TO ATM

    RESET POWER TO ATM

    01

    WD

    0400

    REMOVED JAMMED CARD

    REMOVED JAMMED CARD

    01

    WD

    0401

    CLEANED CARD READER

    CLEANED CARD READER

    01

    WD

    0402

    CLEARED CARDS IN BIN

    CLEARED CARDS IN BIN

    01

    WD

    0404

    CARD READER IN SERVICE

    CARD READER IN SERVICE

    01

    WD

    1102

    DES KEYS REMOTE ENTRY

    DES KEYS REMOTE ENTRY

    01

    WD

    1103

    DES KEYS ENTERED

    DES KEYS ENTERED

    One of the problems that I noticed is that the person that created the problem codes (PR) and the work done (WD) codes duplictaed the codes I.E. there is a 0404 for the Problem Code as well as a 0404 for the Wrok Done Code. So when I try to do the formula below I get both the PR and the WD codes.

    if {SBCODE.SCDTYP} ="PR" then {SBCODE.SCDDS2} Else ""

    This should work, but it doesn't. If I use the Select Expert and set {SBCODE.SCDDS2} = "PR" then I get the right results for the Problem Code Description, but then I cannot get anything for the Technician Names {SBCODE.SCDTNM} because SBCODE.SCDTYP needs to be set to "TE" for the Technician Names and "WD" for the Work Done Descriptions.

    Thanks for your help

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 02, 2007 at 01:54 PM

    Post Author: V361

    CA Forum: General

    Sure, if you can send the report, that would save me some time. (you can check save data with report) if nothing is confidential. I can also try inserting extra sections in the report and just supressing the ones I don't want to see, (Instead of the subs)

    For the subs, lets say you have a working report that returns the PR's. Save that report, then insert a sub, create from existing, and select the PR report, then edit the subreport and change the select from PR to WD. Your subreport, should look quite a bit like the PR report, but it will have WD data because of the select statement.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 02, 2007 at 02:18 PM

    Post Author: V361

    CA Forum: General

    I also created a group on SCDTYP and that seems to work fairly well, it puts all the PR, TE, WD.... etc into seperate groups ....

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.