Searching for BANK-STATUS field - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

ric9d

Veteran Member

Posts: 76

2/26/2016 7: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

Veteran Member

Posts: 76

2/26/2016 8:02 PM

My user can download it using Microsoft AddIns. APPAYMENT, BANK-STATUS

mikeP

Veteran Member

Posts: 151

2/26/2016 8:10 PM

Data File Text also shows a derived fields with that name APAPPHIST.

Chris Martin

Veteran Member

Posts: 277

2/26/2016 8: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

Veteran Member

Posts: 1498

2/26/2016 8: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

Veteran Member

Posts: 76

2/26/2016 8: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

Veteran Member

Posts: 76

2/26/2016 8: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

Veteran Member

Posts: 1163

11/11/2016 8: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?

Greg Moeller

Veteran Member

Posts: 1498

11/11/2016 9: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

Veteran Member

Posts: 1498

11/11/2016 9: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

Advanced Member

Posts: 23

11/11/2016 9: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

Advanced Member

Posts: 23

11/11/2016 10:00 PM

Ok, so I found them in the gen database... FILECND, FILECNDELM. They don't really make much sense though.