1 2 Previous Next 17 Replies Latest reply on Nov 15, 2013 8:29 AM by RichNasser

    Trying to lookup values into a portal

    RichNasser

      Title

      Trying to lookup values into a portal

      Post

           Hi,

            

           I have a table RecipeIngredient that has RecipeID IngredientID and Qty to signify lines in a recipe.  The ingredients and recipe IDs are from self-joined TOs (Recipe table).  This mimics the BOM to model recipe components and sub-components.

           Also, I have a Lookup field in RecipeIngredient that gets the value of the Description of the ingredient/recipe from the Recipe table

           What I can't do:

           From RecipeIngredient layout context I have created a portal (by using a self-join TO of RecipeIngredient). I'd like to display the recipe or ingredient description using the lookup, but I also want the recipe or ingredient id as well.  Somehow  when I create a lookup for the ID, both lookups are not related.

           I need the description so users can locate ingredients by name, but I need the ID to create the RecipeIngredient line.

            

           Thanks

      Screen_Shot_2013-11-12_at_1.30.20_PM.png

        • 1. Re: Trying to lookup values into a portal
          philmodjunk

               I like how you you've used the same data source table for Recipe's and Ingredients and I get how that kind of "recursive" relationship can work to document the recipe for items that are then ingredients for more complex recipes. (You can have a recipe for "frosting" and that, in turn, can be an ingredient for a frosted cake....)

               Why are you creating new records on this layout instead of in a portal on the Recipe layout? That approach would automatically enter the correct value into the Recipe_Ingredient::recipe_id field whn you select an ingredient using the Recipe_Ingredeint::ingredient_id field.

               That said, you can format both the id fields in Recipe_ingredient with value lists where the value from field 1 is the id number and the value from field 2 is the description. Since both Recipe and Ingredients are really the same table, you can use the same value list for both fields. This allows you to select a recipe or an ingredient by name but the value list enters the ID number and thus links the join table record to the correct parent record (either a recipe or an ingredient).

               But I am also aware that for long lists of values, it can be very tedious to find and select the correct value from the list. Thus a modified version that uses an auto-complete enabled drop down list of description with a script and added relationship to look up and enter the correct ID will be much nicer. See this demo file:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               And there are even more sophisticated methods that can be used if you set up "search portals" instead of value lists. These list items that partially match search text in a field above the portal that update with each additional keystroke to show fewer and fewer matching values. Clicking a portal row then selects that value and enters its ID into the appropriate id field.

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               FileMaker 12 users can open this file from the File menu to get a copy converted to the .fmp12 format.

          • 2. Re: Trying to lookup values into a portal
            RichNasser

                 PhilModJunk thanks.  I started to do the portal from the Recipe table but then got mixed up and went from the RecipeIngredient.  So now, I am adding a RecipeIngredient portal from the Recipe context.  I didn't quite understand your suggestion: 

            "That said, you can format both the id fields in Recipe_ingredient with value lists where the value from field 1 is the id number and the value from field 2 is the description."

            Are you referring to the value list option that allows you to show both values from the same field? I tried doing this and I don't think I understood your suggestion...

                  

            BTW, those examples are out of this world!  Shows the true flexibility of FM!

            • 3. Re: Trying to lookup values into a portal
              philmodjunk

                   When you set up a value list, selecting the "use values from a field" option, you can select values from two different fields. In your case, you'd select RecipeID as the first field and the description field as the second field. You can choose the option to only show values from the second field, but only if the text in the description field is unique as otherwise, duplicate values are omitted from the value list.

                   This value list enters the ID number even though you are using the description in order to select the value so you would format your two ID fields with this value list if setting this up on a layout based on the join table.

                   This same process works for a portal to the join table, but you only need format the ID field for ingredients in the case of using the portal. "allow creation of records..." is usually enabled for the join table in the recipe to join table relationship to simplify the data entry process.

              • 4. Re: Trying to lookup values into a portal
                RichNasser

                     got it... but then you still have the problem of auto-complete function not being enabled, which I need.  So now I am studying and admiring both the examples you sent to see if I can implement.

                     In 'Further Reading' of the Simple Name Lookup you do give the caveat of not using names - I can force the users to ensure unique names are inputted in the names field of recipes as we have full control of that (perhaps checking for dupes anyways would be a good safeguard).

                     Again thanks for your help on this...


                     Rich

                • 5. Re: Trying to lookup values into a portal
                  philmodjunk

                       Enforcing uniqueness is only part of the reason for using ID's in place of names as match fields in your relationships.

                       Let's say you enter a new record and enter a new name. Several days or more later, you notice that the name was not entered correctly. You then edit the name to correct the error and promptly "break" the link to any related records that are still using the incorrectly entered name to match to it.

                       The ideal primary key is:

                         
                  1.           Absolutely, positively unique.
                  2.      
                  3.           Devoid of any additional "meaning" beyond uniquely identifying a record in the table.
                  4.      
                  5.           Never, ever, ever changed after a value is assigned to the pk field.
                  • 6. Re: Trying to lookup values into a portal
                    RichNasser

                         Got it.  For purposes of lookup only, I have created a new TO for Ingredient to match it to a field I call SelectIngredient in my RecipeIngredient table (hoping this auto-populates the field).

                          

                         I have a Layout in the Recipe context with a portal to RecipeIngredient with Select Ingredient in the portal that uses the value list for Recipe Description in Ingredient 2 table.

                          

                         I also have a script setting the field SelectIngredient to put the value there from Ingredient2::Recipe Description.


                         However, nothing happens (I have to OnRecordLoad) and added beep to tell me when it's loading.  

                          

                         Probably not doing something right.

                          

                         Any suggestions? Hate to keep buggin you with this....

                          

                    • 7. Re: Trying to lookup values into a portal
                      philmodjunk

                           Are you trying to implement the method demonstrated in the SimpleNameLookUp demo?

                           Is your script set to be performed by the OnObjectSave trigger on the name drop down field?

                           You may need to post your script:

                           To post a script to the forum:

                             
                      1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                      2.      
                      3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                      4.      
                      5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                      6.      
                      7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                      • 8. Re: Trying to lookup values into a portal
                        RichNasser

                             I seemed to have scrapped the script and the new Ingredient 2 table and relationship.  I was trying to implement the simpleNameLoopkup - I think I am going to opt for a simpler solution to add the components of the recipe to the Recipe Ingredient table using a script...

                             thanks for your help on this...really.

                        • 9. Re: Trying to lookup values into a portal
                          RichNasser

                          blush - so I went back to try the simpleNameLookup in a "test" layout and was not successful.  I got the lookup to work in a SelectIngredient.  But taking a look at what I am trying to accomplish:

                                

                               From a Recipe Layout I would like to display the current Ingredient and then have a button to add a subcomponent and have it display in a RecipeIngredient Portal where items such as Qty could be changed or the entire portal row deleted.  So doing the simpleNameLookup in a portal scenario proved to be a little hairy.

                               Here is the current state of tables and the relationships...

                                

                          • 10. Re: Trying to lookup values into a portal
                            philmodjunk

                                 That relationship won't work. You need two separate relationships. One that matches by recipeID and one that matches by a text field, SelectIngredient that matches to your existing Recipe Description.

                                 This will require adding a third occurrence of your Recipe table in order to have a separate relationship.

                            • 11. Re: Trying to lookup values into a portal
                              RichNasser

                                   OK _ got it to work.  The only thing I need to figure out is why is the SelectIngredient defaults to the Recipe Description when I select it.  To select another ingredient I have to re-do the drop down for it to 'stick' -

                              • 12. Re: Trying to lookup values into a portal
                                philmodjunk

                                     And which field in your layout is SelectIngredient? The drop down list in the portal?

                                     Under what circumstances does it "default to the recipe description"? Do you mean it shows "Jalapeno Whole" in each new record in the portal? Which field and from which table occurrence is shown as the number field to the left of the drop down?

                                • 13. Re: Trying to lookup values into a portal
                                  RichNasser

                                       SelectIngredient is the dropdown in the portal.

                                        

                                       Yes when I select a new value from the dropdown (ie SALT) it instantly shows JALAPENO when I let go of the mouse. It does this in each new record in the portal.


                                       The number to the left is ingredient_id which is from the RecipeIngredient table and is a lookup to the Recipe 2::RecipeID.   

                                       The relationship is as follows -> RecipeIngredient::ingredient_id=Recipe 2::RecipeID

                                        

                                  • 14. Re: Trying to lookup values into a portal
                                    philmodjunk

                                         Is there an auto-enter field option specified for SelectIngredient? If so, what?

                                         Is there a script performed by a script trigger when you select a value from the list? If so, please post the script.

                                         To post a script to the forum:

                                           
                                    1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                                    2.      
                                    3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                                    4.      
                                    5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                                    6.      
                                    7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                                    1 2 Previous Next