PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/11/2016 5:00 PM by  Greg Dey
Searching for BANK-STATUS field
 11 Replies
Sort:
You are not authorized to post a reply.
Author Messages
ric9d
Private
Private
Veteran Member
(202 points)
Veteran Member
Posts:76


Send Message:

--
02/26/2016 2:56 PM
    Hello all,
    I am looking for the BANK-STATUS field for my report. Please help as I dont know where to get it from (table and field names pls!).

    The possible values are "Reconciled", "Issued", etc.

    THANKS!
    Ric
    ric9d
    Private
    Private
    Veteran Member
    (202 points)
    Veteran Member
    Posts:76


    Send Message:

    --
    02/26/2016 3:02 PM
    My user can download it using Microsoft AddIns. APPAYMENT, BANK-STATUS
    mikeP
    Private
    Private
    Veteran Member
    (407 points)
    Veteran Member
    Posts:151


    Send Message:

    --
    02/26/2016 3:10 PM
    Data File Text also shows a derived fields with that name APAPPHIST.
    Chris Martin
    Private
    Private
    Veteran Member
    (825 points)
    Veteran Member
    Posts:277


    Send Message:

    --
    02/26/2016 3:13 PM
    It's a derived field, so it's not stored in the database (but can be accessed via Addins as ric9d mentioned). You can run the derfldrpt utility from the command line to display the logic, which you can then use to recreate it in your report (using a CASE statement, for example).
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    02/26/2016 3:18 PM
    Here's the formula that derfldrpt produces... *Caution: My run is old, but it shouldn't change that much.
        Conditional Field - BANK-STATUS   
    
             If (NO-PAYMENT)
                 "UNPAID"                    Value
             Else
             If (CHK-PROCESSED)
                 "PROCESSED"                 Value
             Else
             If (RECONCILE-CASH)
                 "RECONCILE"                 Value
             Else
             If (VOID-CASH)
                 "VOID"                      Value
             Else
             If (STOP-PMT-CASH)
                 "STOP PAYMENT"              Value
             Else
             If (STALE-DATED)
                 "STALE DATED"               Value
             Else
             If (ESCHEATED)
                 "ESCHEATED"                 Value
             Else
             If (UNACCEPT-DRAFT)
                 "UNACCEPTED"                Value
             Else
             If (ACCEPT-DRAFT)
                 "ACCEPTED"                  Value
             Else
             If (CASHED-DRAFT)
                 "CASHED"                    Value
             Else
             If (DISHONOR-DRAFT)
                 "DISHONORED"                Value
             Else
             If (CANCEL-DRAFT)
                 "CANCELLED"                 Value
             Else
             If (TAPE-PMT-REJ)
                 "EFT REJECTED"              Value
             Else
             If (TAPE-VOID)
                 "EFT VOIDED"                Value
             Else
             If (ISSUED-CASH)
                 "ISSUED"                    Value
             Else
                 Spaces                      Value
     
    ric9d
    Private
    Private
    Veteran Member
    (202 points)
    Veteran Member
    Posts:76


    Send Message:

    --
    02/26/2016 3:24 PM
    Thanks!

    However I am not sure how to interpret this?
    Where do I get the "(NO-PAYMENT)", "(CHK-PROCESSED)", etc?
    Are these values or (representing a) field in the DB?
    ric9d
    Private
    Private
    Veteran Member
    (202 points)
    Veteran Member
    Posts:76


    Send Message:

    --
    02/26/2016 3:38 PM
    Thanks!

    However I am not sure how to interpret this?
    Where do I get the "(NO-PAYMENT)", "(CHK-PROCESSED)", etc?
    Are these values or (representing a) field in the DB?
    JonA
    Private
    Private
    Veteran Member
    (2766 points)
    Veteran Member
    Posts:972


    Send Message:

    --
    11/11/2016 3:07 PM
    That report makes no sense to me. How does the derived field report for BANK-STATUS translate to some sort of IF/THEN statement in Crystal?
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    11/11/2016 4:23 PM
    @JonA: I'm sorry I'm not much help. I'm just showing you the output of %GENDIR%\bin\derfldrpt prod which I was told gave you the formulas for all of the derived fields in Lawson/Infor.
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    11/11/2016 4:26 PM
    Going back I find that BANK-STATUS is only shown in two places (two tables) APPAYMENT and APAPPHIST --- That second one sounds like a staging table for a AP500-type interface job. I can't find fields in the database that correspond to anything like NO-PAYMENT either....
    Greg Dey
    Private
    Private
    Advanced Member
    (60 points)
    Advanced Member
    Posts:22


    Send Message:

    --
    11/11/2016 4:48 PM
    The derived fields get buried in the old Lawson stuff pretty quickly. Seeing how I'm old and been working with Lawson... I use dbdef but I realize not everyone has access to dbdef. I will look further for tables later but for now you see the "calculation" for the derived field by looking at element value in dbdef (f6 on the filed and choose element) . BANK_STATUS on appayment has several including No_payment, Chk_processed, reconcile_cash, etc. These are defined in the Conditions for appayment or they might be a combination. For instance No Payment is defined as "when Trans_nbr = Spaces". Some conditions can be simple or they reference relations or several relations but it will say "Relation:" in the condition. I thought they could contain other conditions as well but I haven't found any yet. So you might have to dig from the table to the column to the conditions to the relations. Or in Lawson terms, from the file to the field to the conditions to the relations.
    Greg Dey
    Private
    Private
    Advanced Member
    (60 points)
    Advanced Member
    Posts:22


    Send Message:

    --
    11/11/2016 5:00 PM
    Ok, so I found them in the gen database... FILECND, FILECNDELM. They don't really make much sense though.
    You are not authorized to post a reply.