10 Replies Latest reply on Oct 17, 2012 2:53 PM by philmodjunk

    Value List on Non-Key Field



      Value List on Non-Key Field


           I have a relationship very similar to the typical Line Item <<----Products. Products has a auto enter Serial No. field as the key. My Line Items is displayed in a portal. I want to use a field called "Short Code" in Line Items to see the related record in Products via a value list and poulate the Line Item record. AND, the "Short Code" field is unique to only the current project, but not across the whole table.  I've figured out how to diplay only the current projects products in the value list. I now need a way for my users to see the "Short Code" which is meaningful to them, but to populate the record based on the Serial No. Seems like this should be fairly simple, but I haven't been able to work around it without a lot of confusion.



        • 1. Re: Value List on Non-Key Field

               the "Short Code" field is unique to only the current project,

               Please explain how the "short code" is supposed to work. Does a given "short code" identify a given product or a group of products?

          • 2. Re: Value List on Non-Key Field

                 First, I think I found a work around by using a calculated field that combines the short code with the project number (and I've formatted so that it's easy to read).  And yes, the "short code" identifies a given product. 

                 What we have is similar to your typical Customer / Order scenario :

                 Project = Customers

                 Headings = Orders

                 Heading List = Line Items

                 Products = Products

                 I'm probably way over-explaining this, but...

                 Basically, I need a custom list of products for each project. However, we don't make the products, we buy them from dozens of vendors and the product options are such that they are rarely the same from project to project except for a few of the basic items.  I have 5 users working on approximately 100 different projects (total) per year.  Each project averages about 40 different products.  I have created basic look up tables for "Description", Product No." and "Finish". Users are allowed to add to the lookup tables. They also have other options that must be added depending on the product and project.  The product list grows fast and searching for a previous product that will exactly match the current one is tedious and usually unsuccessful.  It's quicker and more accurate to let the user build a new product list for each project and apply a "short code" for the product that's meaningful to them in the data entry process. Also, I have managed to get the value list to show products from only the current project.  Just this shortening of the list is very helpful. 

                 Because the "short code" may be reused by the same or different user on different projects, it can't be unique. Therefore I am using the SerNo.as the key field.  I can make the short code show up on my value list, but this won't populate record in portal as the link is to the serial no. 


            • 3. Re: Value List on Non-Key Field

                   I'm not sure that I see the need for the short code in terms of this particular layout design. If your user has already selected a project from the list of open projects, then that is sufficient info to set up a conditional value list that only lists products for that project (and it can include "basic" products specifed to be available for all projects.)

              • 4. Re: Value List on Non-Key Field

                     Hi Phil, I'm finally getting back to this problem. I'm trying to preserve the one to many rule. I've got the conditional value list based on the project working.  To get a OtM relationship, I need a unique value on one side. I have a serial no and I've found a way to build a unique product number. But what I want for my user is to use the short code as the link to populate the many side.  But the short code can't be unique, because I may want to use it again on a different project.  I can make this work just fine now, but only if I link the talbes via the short code which gives me a MtM relationship. Does this make any sense? Am I just being to picky with the short code issue or am I missing sometning obvious?



                • 5. Re: Value List on Non-Key Field

                       I don't understand how the "short code" is expected to work and how it is intended to make this easier for the user. Can you post an example?

                  • 6. Re: Value List on Non-Key Field

                         Hi, I hope you're not sorry you asked. I have uploaded some info.  But you should know that records in the product table are at first built using lookups from other tables - mostly generic information such as a description and the very basic product number.  Once in the product table, they records have additional fields with differtent attributes added: sizes, finish colors and options (also all lookups) specific to the current project.  This makes it difficult (and unlikely) to share a product record for one project with another project.   So using lookups, the user builds a list of products that are basically unique to the project they are currently workng on.  When it comes to the data entry part (HeadingHardware - see attached), I'm trying to find a simple code (a few keystrokes) get the items from the value list without having to look at a serial number or pick the actual product number which can be very similar and longer than what the value list will display.  Typically, the user has a list of "short codes" for every heading for data entry. 

                         301 = 7100 x AU626F x P x 3' x 7 x VKC2 x PCS

                         302 = 7100 x AU626F x P x 3' x 7 x VKC3 x PCS

                         In my old DB life, I could make the prodCode (short code) a "composite" key with another field in child table to get my OtoM...

                    • 7. Re: Value List on Non-Key Field

                           Sorry, My upload was a pdf. I'll try it again as a jpg

                      • 8. Re: Value List on Non-Key Field

                             Why would you want to reuse the code? This would seem to be a very bad idea. I'd go with a new and unique code every time.Here's why, if you "reuse" that short code to identify a different product, the existance of a second, older product record with the same code means that this product is the one that will be looked up instead of the new one unless you either delete that product record or change its code--and in which case you can specify unique values anyway.

                             But I'd also use search and selection methods where you do not need to enter any code at all.

                             Consider this value list:

                             Specify a value list with the "use values from a field" option.

                             Select ProductTable as the table for your values and select ProdCodeUnique as the field. Then select the "also use values from" check box and select a text field that displays a name and/or description of the product. (This text should also be unique and you may need to define a text field with an auto-entered calculation to combine data from several fields.)

                             With this value list, there's an option you can select to hide the first field, ProdCodeUnique so that the user never sees it. They select a product from the text that is listed, but the value list enters the unique product code, which should then be used in your relationship to match to a specific product record.

                             What I have described is the most basic, simple way to set this up. It's best suited for short value lists. As the potential list of values grows there are other approaches that use scripts and additional relationships to make selecting an item by name, but entering the ID number a more user friendly process by enabling a process where entering more and more text filters the list down into a shorter and shorter list of values to select.

                        • 9. Re: Value List on Non-Key Field

                               Thanks Phil. I think you'ver confirmed what I expected. I already have a working model with the ProductUnique and "also use values from" and it does the trick.  I was hoping I could link on a unique field (ser no?) but still display and populate based on the prodcode.  This worked in old life ("appeared" to work), but that was because each project was actually it's own separate data base file.


                          • 10. Re: Value List on Non-Key Field

                                      I was hoping I could link on a unique field (ser no?) but still display and populate based on the prodcode

                                 You can, but if the prod code alone is used in the relationship, it must be unique or your results will be inconsistent. This would be true of any relational database not just fileMaker.

                                 If you included a second pair of fields in your relationship--then this might be possible, but simply using the unique id number would seem a much simpler approach.