Using non-primary key as match field.

[usac24]

Hello all!  First time poster here.  I've built several Filemaker databases over the years, but have never had this issue.  By the way, using Filemaker 13 on OS X 10.9.

 

Background:

I'm building an inventory database for my wife's company.  Her business sells retail widgets.  She usually has several thousand times in stock.  The unusual aspect (for my purposes) of this business model is that every widget is unique and has it's own serial number.  The serial number contains data with related info for that particular widget.  I am trying to create this solution so that as an invoice is created, the widget's bar code is scanned and entered into the line item portal on the invoice, the related data (category, description, price) from the Widgets table is displayed on this line item.

Problem:

When I tab to the line item in the portal, I enter the serial number in appropriate field, but I cannot get get the remaining, related fields I am looking for to display.  The ID_SerialNumber field is indexed, but it is not the primary key for the Widgets table.  I have a field call ID_Widget (serialized, auto-enter on creation) which is the primary key.

I have found a workaround (see pictures), by which I include a field in the portal, LineItems::IDf_Widget.  By entering the associated number for the primary key, the remaining data autofills in the portal fields just like I want.  However, this is not a practical solution because the intent is to scan barcodes to generate the invoices rapidly.  With thousands of serial numbers, I cannot see a convenient method to enter only the related primary key field.

I have attached a picture of the database relationships and a shot of the invoice entry layout.  I appreciate any and all help.

Thanks!

[comment]

every widget is unique and has it's own serial number.

 

If the serial number.is known to be both permanent and unique, why don't you use it as the matchfield for the relationship? For that matter, why do you need another primary key for that table at all?

 

 

Note also that with unique items, the line items table is pretty much redundant. You could simply insert the InvoiceID value into a field of Widgets to indicate it's been sold. Unless you're planning returns and resales, that is.

[usac24]

If the serial number.is known to be both permanent and unique, why don't you use it as the matchfield for the relationship? For that matter, why do you need another primary key for that table at all?

 

That works perfectly!  My hesitation to try this was that it set the relationship between the Widgets table and LineItems table as a many-to-many, since there is no way to set the ID_SerialNumber field as the primary key.  I guess I was trying to maintain the context of "one line item can contain one widget, and one widget can appear on only one line item" unnecessarily.  (Or as you suggested, one invoice can contain many widgets, and one widget can appear on only one invoice).

 

Looks like I lost sight of the forrest for the trees.  Am I safe in assuming that the primary key is unnecessary if I am confident my Serial Numbers are permanent and unique?

 

Thanks for the help!

[comment]

Am I safe in assuming that the primary key is unnecessary if I am confident my Serial Numbers are permanent and unique?

 

If you are confident that is so, then the Serial Number can be your primary key (i.e. you do not need a Filemaker-generated serial number). To increase your confidence, set the field to validate as unique - this will also fix the display of your relationship graph (although this is strictly a cosmetic issue).