• From: Panama
  • Registered: 03/16/2015
  • Posts: 12

Topic: Include memo_ from gl_trans table in bank statement

Hello,

[Using FA 2.3]

I would like to know how I can get memo_ from the gl_trans table to show up in the bank statement report.

Seems like the bank statement report is made using the bank_trans table, which does not include the memo_ field.

There is no problem for entries created using payment or quick entry because they pull the person_id from the form. However, whenever I create an entry using the journal entry in the banking and general ledger section, and then proceed to print a bank statement, the memo_ field that I entered does not appear in the report.

I would like to include memo_ in the report for those entries that I create with journal entry. As of now, nothing show up for this case.


Thank you.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Include memo_ from gl_trans table in bank statement

Make a mockup of with and without the memo_ field and paste the link to the image here so that we can advise you. FA v2.4 now in development state now supports the person_id in these reports (hopefully).

apmuthu's Website

3 Reply by hunter12 (edited by hunter12 03/27/2015 07:10:23 pm)

  • From: Panama
  • Registered: 03/16/2015
  • Posts: 12

Re: Include memo_ from gl_trans table in bank statement

Without memo_, the banking report looks like this:

Type                      #     Reference         Date               Person/Item       Debit         Credit           Balance

Bank Deposit        6        2                       03/03/2015      john                  100.00                        100.00
Journal Entry       28       7                      03/23/2015                                100.00                        100.00

Person/item gets its data from the person_id and person_type_id fields in bank_trans table.

With the memo_ field it should print this:

Type                      #     Reference         Date               Person/Item       Debit         Credit           Balance

Bank Deposit        6        2                       03/03/2015      john                  100.00                        100.00
Journal Entry       28       7                      03/23/2015       george              100.00                        100.00



where george is the string stored in memo_ when creating the journal entry.

What I ultimate want is to be able to retrieve memo_ from the gl_trans table using the  keys from the bank_trans table that are present in gl_trans table. This would be only for journal entry since other transaction forms innclude the field for entering the personś id and therefore show up in the banking report.


Thank you.

  • From: Panama
  • Registered: 03/16/2015
  • Posts: 12

Re: Include memo_ from gl_trans table in bank statement

I found a workarounf for this. I modified one of the functions in the gl_db_bank_trans.inc file to retrieve the memo_ field of a transaction and then I call that function in the banking report using the account_code variable.


Thank you.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Include memo_ from gl_trans table in bank statement

Excellent, care to post the code?

apmuthu's Website

  • From: Panama
  • Registered: 03/16/2015
  • Posts: 12

Re: Include memo_ from gl_trans table in bank statement

Sure.

In gl_db_bank_trans.inc, I added the following function to retrieve memo_:

function get_gl_trans_memo($account, $type, $trans_no)
{
    $sql = "SELECT memo_ FROM ".TB_PREF."gl_trans WHERE account="
    .db_escape($account)." AND type=".db_escape($type)
    ." AND type_no=".db_escape($trans_no);

    $result = db_query($sql, "query for gl memo_");

    $row = db_fetch_row($result);
    return $row[0];
}

--------------------------------------------------------------------------------------------------------------------------------------------------------

In rep601.php for banking report, I modified the code inside the while loop (see below) and I used the parse function provided by apmunthu to retrieve all strings stored in memo_ except the variable_values(I used the parse function  in another report that is generated through journal entry and whose variable-values I want to see).

                      while ($myrow=db_fetch($trans))
            {
                if ($zero == 0 && $myrow['amount'] == 0.0)
                    continue;
                $total += $myrow['amount'];
                               
                                $name = get_gl_trans_memo($ident,$myrow['type'],$myrow['trans_no']);  //Retrieves memo corresponding to bank transaction.
                                $name_to_store = parse_notes_params($name); //Parses memo_ string
                               

                $rep->TextCol(0, 1, $systypes_array[$myrow["type"]]);
                $rep->TextCol(1, 2,    $myrow['trans_no']);
                $rep->TextCol(2, 3,    $myrow['ref']);
                $rep->DateCol(3, 4,    $myrow["trans_date"], true);
                               
                               //Handles case for bank transaction created through a form different than journal entry.
                               if (payment_person_name($myrow["person_type_id"],$myrow["person_id"], false) == "" ){
                                    $rep->TextCol(4, 5,$name_to_store['notes']);
                                   
                                   
                                }
                               
                                $rep->TextCol(4, 5,payment_person_name($myrow["person_type_id"],$myrow["person_id"], false));
                             


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

7 Reply by apmuthu (edited by apmuthu 04/08/2015 04:58:00 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Include memo_ from gl_trans table in bank statement

Modify your function get_gl_trans_memo as follows:

function get_gl_trans_memo($account, $type, $trans_no)
{
    $sql = "SELECT COALESCE(memo_, '') AS memo_ FROM ".TB_PREF."gl_trans WHERE account="
    .db_escape($account)." AND type=".db_escape($type)
    ." AND type_no=".db_escape($trans_no);

    $result = db_query($sql, "query for gl memo_ failed");

    $row = db_fetch_row($result);
    return $row[0];
}

This will help if the memo_ field is NULL.

apmuthu's Website

  • From: Panama
  • Registered: 03/16/2015
  • Posts: 12

Re: Include memo_ from gl_trans table in bank statement

Thank you!