Over 46,000+ Business Solution Developers Find answers, ask questions, and connect with our community of business solutions developers, business owners and partners.
I have done this a number of times in the past but thought I would take a moment to document it here. I’ve designed dozens of purchase order modules for FileMaker users of the years. One thing that commonly identifies a purchase order is the purchase order number. This number needs to be unique to each order, just like an invoice number is unique to an invoice. The aspect that it has to be unique for each record makes it an attractive option for making the purchase order number field my primary key field.
Thing is, some customers want the purchase order to convey some sort of information about the order itself. So the coding of the purchase order number might include a date value, a vendor id, the person placing the order, the location the order is going to and what not. Although unlikely, it is possible that we might get some sort of duplicate purchase order number. Also, some customers want to be able to edit the purchase order number and it is their database solution. As their developer, I have to be able to bend to their will but still keep the integrity of the data.
I cannot allow my customers to change key data unless it is to fix a problem (like an import gone horribly awry). Even then, the answer is no, no, no, maybe, no, no and then finally OK.
To allow a custom to noodle with some unique (like a po number), I tend to use an “under the hood” incremental field as my primary key field and don’t use the purchase order number in any relationship setup. The same rule would follow for invoice number, production job number, requisition numbers and the like.
© 2008 – Dwayne Wright – dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.