2 Replies Latest reply on Jul 22, 2014 11:28 AM by philmodjunk

    Need to display related fields in portal

    RobertParker

      Title

      Need to display related fields in portal

      Post

           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 related info for that 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, the related dated (category, description, price) from the Widgets table is displayed on the 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 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!

            

      Screen_Shot_2014-07-21_at_12.06.45_PM.png

        • 1. Re: Need to display related fields in portal
          RobertParker
          /files/91b0da4225/Screen_Shot_2014-07-21_at_12.09.16_PM.png 964x347
          • 2. Re: Need to display related fields in portal
            philmodjunk

                 And is this a portal to LIneItems on the Invoices layout? (I will assume that this is the case...)

                 the intent is to scan barcodes to generate the invoices rapidly

                 Option 1 is to scan the barcode into a global field. A script then creates the lineitem record and uses the barcode to look up the needed value for IDf_Widget. A relationship using the global field as a match to an occurrence of Widgets also linking by the manufacturer's serial number can make looking up the needed primary key value.

                 Option 2 is similar but you put the field into which you want to scan the barcode in the LineItems table as a regular field and use it as a match field to a different occurrence of Widgets so that it can match by MFG serial instead of ID. Then the IDf_Widget can use an auto-enter setting to automatically look up the needed ID and thus link to your existing occurrence of Widgets.

                 Option 3 is to break the rules and just match by the MFG serial instead of the internally generated primary key. Don't use this method without giving the consequences careful thought.

                 PS. this may be unusual to you, but car dealers deal with this issue all the time as they have to track inventory by VIN for each individual vehicle. wink