• p2409
  • Senior Member
  • Offline
  • From: Australia
  • Registered: 12/11/2008
  • Posts: 127

Topic: Use of type, trans_no and ref in system

Hello fellas

I'm looking into the beginnings of an API to post transactions. I've seen the downloads for customer and item importation on this site in the download section - these are relatively straightforward, however sys_type updates are obviously more complex as counters, next refs, taxes etc need to kept up to date. I don't think anyone's done this yet through an API-style interface.

A few questions for the experts:

1) In the 1_sys_types table, what is the difference between 'type_no' and 'next reference'? I note that sometimes they are in sync, but in the empty US SQL import file, they aren't always.

2) Am I right in saying that sys_type and type_no are THE definitive keys for any transaction in the system. Does this mean that type_no and next_reference should stay in sync? How would they get out of sync?

3) Is 1_refs table? just a text 'reference' information field against a transaction keyed by a sys_type and id number?

Thanks boys I'll let you know how I go.....

Pete

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Use of type, trans_no and ref in system

Hello Pete,
You are quite right in your assumptions.
The type and trans_no are unique for the docuemnts and other operations and this type and trans_no follow the transactions all the way down to GL Entries.
The 'refs' are for your convenience. You an use an alternative numberserie starting f.i. from 1000. You can also use a combination of letters and/or digits. In case of only numbers, the refs are automatically increased everytime you use it. Otherwise you see the last one.
This is very handy for documents like invoices.

/Joe
BTW. in the file /gl/includes/db/gl_db_trans.inc, a bit down,  you can see how to insert a complete Journal Entry.

3 Reply by p2409 (edited by p2409 06/12/2009 09:01:57 am)

  • p2409
  • Senior Member
  • Offline
  • From: Australia
  • Registered: 12/11/2008
  • Posts: 127

Re: Use of type, trans_no and ref in system

Thanks Joe. While reading your answer, I thought up (coded up) a possible enhancement to cater for eg. INV0034 or DPA11023 type references that still allows for 'counting up' as payments etc are made.

Basically, if the ref ends in digits (and digits only), then the function will increment by 1, while still keeping the alpha prefix.
So: DPA0023 becomes DPA0024, INV0000332 becomes INV0000333 and 45TR34P009 becomes 45TR34P010.

Here's the new references.inc class method for increment($reference) (line 72)

    function increment($reference) {
        // If $reference is trailed by digits, and digits only,
        // extract them and add 1, then put the alpha prefix back on
                // NB. preg_match returns 1 if the regex matches completely
                // also $result[0] holds entire string, 1 the first captured, 2 the 2nd etc.
        if(preg_match('/^(.*?)(\d+)$/',$reference,$result)==1) {
            $dig_count = strlen($result[2]); // How many digits? eg. 0003 = 4
            $fmt = '%0' . $dig_count . 'd'; // Make a format string - leading zeroes
            $nextval =  $result[1] . sprintf($fmt, intval($result[2]+1)); // Add one on, and put prefix back on
            return $nextval;
        }
        else
            return $reference;
    }
    //------------------------------------
}

You can get rid of comments, or do anything (or even ignore it if you think it's not worth it). I'm using it because my pre-printed stationery has a 3-letter alpha code at the front.

Cheers
Pete

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Use of type, trans_no and ref in system

A very good idea, Pete. I will include it at once to go with 2.1.3.

/Joe