Jump to content

Using non-primary key as match field.


usac24
 Share

This topic is 2570 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!

post-111570-0-13309700-1405974895_thumb.

post-111570-0-25143700-1405974896_thumb.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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!

Link to comment
Share on other sites

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).
Link to comment
Share on other sites

This topic is 2570 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By Richard Carlton
      Confused on how you should set up your relationships in FileMaker? In Day 1 of this Livestream, we slowly and methodically cover Claris' officially recommended relational design process, Anchor Buoy! Future live streams: https://fmtraining.tv/#LIVE 
      [email protected]
      www.fmtraining.tv
      www.fmstartingpoint.com
      www.rcconsulting.com 
       
    • By Sinky
      Hi!
      I have a calculation field in a portal which shows the value according to ActivePortalRowNumber. Each row has a special determined value.
      When I manually enter the value in the field before the calculation field and thereby make a particular row active, my calculation field automatically shows the designated value. Great!
      But, if when I do the import from Excel, all data gets nicely imported and the portal shows the imported data but the calculation field is without value. I guess the importing process is not making a portal rows active so the calculation is not working.
      Any ideas how this can be solved?
      Many thanks!
    • By Mafia2020
      I'm not even sure to describe under which category my specific problems fall into, I can only advance some hypothesys, so I'll just describe it and let the wisdom of others guide me.
      I have a DB with several tables, I use them to make invoices. With every invoice I input the desired price and it adds automatically 19% taxes and produces a neat invoice which I can print, these three tables are called:
      Invoices 2018
      Invoices 2019
      Invoices 2020
      Now on to my problem. I wanted to create a forth table where from every previously listed table it would calculate the total revenue of the year in question, broke down like this:
      - Total Net price paid for 2018
      - Taxes for 2018
      - Total revenue for 2018 (net+taxes)
      - Total Net price paid for 2019
      - Taxes for 2019
      - Total revenue for 2019 (net+taxes)
      - Total Net price paid for 2020
      - Taxes for 2020
      - Total revenue for 2020 (net+taxes)
       
      I am unsure how to proceed, I tried creating a forth layout and a new table but I have problems brining the summary field from each year's table into the forth table/report/layout.
    • By Tony Diaz
      The tables.
      Items::ID
      Items::Item
      Items::Publisher
      Items::Date
       
      The Genre data is already arranged like this:
      Genre::ID (Unique Key)
      Genre::Name
      Genre::Category_ID
      Genre::Category

      The Platform data is 'simple'. ID and Name. But each item could be multiple platforms.  (Example #1 vs. Example #2)
      Platforms::ID
      Platforms::Name
      An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples.

      It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selection from. Most have 4-6 of them.

      Example 1: Items::Table on the left, Genre::Table on the right, with some Genre Categories (Genre, Perspective, Pacing, Gameplay, Interface, Setting) and their sub-options.
      This one has just one sub-option per category.

      Example 2:  The Gameplay Genre Category has two sub-options related to it.



      Example Genre Table content:

       
      Just cracking the surface on One to One and One to Many relationships, I don't think this scenario is quite covered this way.

      Would each of those Genre Categories be portals showing only their related category ID?

      I presume that I would add fields to the Items::Table so I can pull related records:
      Items::Platform_IDfk
      Items::Genre_IDfk
      Items::Category_IDfk
      But those only support one relationship.
      Would I make value lists from those Genre Categories and Platforms and set them as tick box fields?
       
    • By stan111
      Gents,
      I use self portal on my Clients layout to serve two things:
      1. display all the records and
      2. quick navigate among them. 
      This portal is not displayed in Webdirect. 
      Is it possible to make it work?
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.