10 Replies Latest reply on Jan 3, 2011 9:18 AM by philmodjunk

    how to get drop-down list from different table or layout?

    a_sun

      Title

      how to get drop-down list from different table or layout?

      Post

      Hi,

      I have 3 .fp files

      Jobtickets.fp
      Line Table 
      => 
      _kf_item_id

      Inventory.fp
      Inventory
      => _kp_item_id

      Purchase Requisition Table
      => _kp_PRid
      => Line::_kf_item_id

      Purchase Order.fp
      => PR::_kf_PRid
      how to get drop-down list from Line::_kf_item_id in PO Table? Filtered by PR::_kf_PRid?
      Meant in PO layout if i select PR id, item drop-down list will be show the related item not all the item list.

      Any idea?

      Thanks,
      suisui

        • 1. Re: how to get drop-down list from different table or layout?
          philmodjunk

          What do you have in Manage | Database | Relationships in Purchase Order?

          Presumably you have:

          PR::_kf_PRid = Purchase Order Requisition::_kp_PRid

          If so, then you have what you need to define a conditional value list.

          Custom Value List? (Use Option 2)

          http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (A knowledgebase article)

          • 2. Re: how to get drop-down list from different table or layout?
            a_sun

            HI Phil:

            Take a look at attached image (Draft Database)
            My issue :
            In PO layout if i selected PR id, item drop-down list will be show the related item NOT all the item list (what i get is ALL list).
            I tried conditional value list method, its shown message that Line::_kf_item_id cannot be indexed, unstored.... MUST be something wrong, please help to check?

            Thanks,
            suisui

            draft database

            • 3. Re: how to get drop-down list from different table or layout?
              a_sun

              Hi Phil:

              I got this error message:
              "This value list will not work because the feild "c_item" cannot be indexed. Proceed anyway?"

              My calculation script: 
              c_item=Case(_kp_PR_id;LINES_planner_PR::_kf_ID_Pap er_Material)

              When I tried to index the field, i got popup message: 
              "The calculation "c_item" cannot be stored or indexed because it refrences a related field, a summary field, an unstored calculation field, or a field with global storage."

              any idea?

              Thanks,
              suisui

              • 4. Re: how to get drop-down list from different table or layout?
                philmodjunk

                First, a little background info:

                All fields that serve as the source for a value list must be stored/indexed. Thus, global fields or calculation fields that refer to global fields or that refer to fields in other tables cannot be used as a value source for you value list.

                I've only got incomplete info here...

                I don't quite get what Line means when you put Line::kf_item_id in multiple entities in your posted diagram. In FileMaker notation, we normally write Line::kf_item_id to mean that the field kf_item_id is from a table or table occurrence named Line. But I see this in 4 different entities in your diagram and none of them are named line.

                More questions:

                How is your Purchase Requisition designed? Based on which entity? Any portals? If so, to what?

                In which entity is Line::_kf_item_id defined and how is it defined?

                Don't have any idea where c_item fits in to all of this. What are you trying to accomplish with this calculation?

                 

                • 5. Re: how to get drop-down list from different table or layout?
                  a_sun

                  Hi Phil;

                  Sorry for my poor description...not sure if attached image can tell you more details?

                  My issue: PO layout if i selected PR id, item drop-down list will be show the related item from Purchase Requisition.help

                  • 6. Re: how to get drop-down list from different table or layout?
                    philmodjunk

                    Let's see if this works and makes sense.

                    The Product ID drop down should only list items already listed in the Purchase Requisition of the same number, _kf_PRid.

                    However, both the purchase requisition and the purchase order layouts have portals to some other table that's used to list the individual items. (I'd be inclined to use the same table for both of these, if possible) That might eliminate the need to select the same items all over again in the Purchase Order portal.

                    That's not to say you may have very good reasons for keeping these records separate if there are significant differences between the items listed in the Purchase Requisition and the Purchase Order--that depends on your business practices as to whether this is an issue.

                    That observation aside, I'm not absolutely sure which table occurrences are referenced by your two portals. I'm still guessing a bit here, but I think you have these relationships:

                    PurchaseOrderPortal TO >----- Purchase Order ------- Purchase Requisition-----< Purchase Req. Portal TO

                    If so, setting up a value list that lists values from the Purchase Req. Portal's TO, Use only related values starting from PurchaseOrderPOrtal TO should produce the desired value list.

                    Note: Table Occurrences (TO) are what we call the "boxes" on your relationship graph.

                    • 7. Re: how to get drop-down list from different table or layout?
                      a_sun

                      Hi Phil:
                      Thank you for helping me, appreciate!

                      I should use the same table for three of these, is my poor database skill.
                      Anyhow, see if still can amend from below relationships? 
                      Else I have no choice and have to redo the Purchase Order Portal relationships. 
                      Any advice?

                      I have these relationships:
                      Purchase Order Portal TO >----- Purchase Order ------- Purchase Requisition-----< Jobticktes. Portal TO

                      Thanks,
                      a_sun

                      • 8. Re: how to get drop-down list from different table or layout?
                        philmodjunk

                        You've repeated from my last post. If those are your relationships, you should be able to get the condtional value list to work as I specified in my last post.

                        My other comments refer to whether you table design is optimum for what you want to do. Here's what I think you are trying to set up:

                        Someone creates a Purchase Requisition. This requisition is much like a purchase order in the data it collects, but must be reviewed and approved before the Purchase Order is created and the items ordered. It's conceivable that one Purchase Requisition results in several purchase orders--each from different vendors, for example.

                        Since you've already listed the items once in the purchase Requisition, why make the user re-enter them all in the Purchase Order? You can either use a script to copy the records from the Purchase Requisition into one or more Purchase Orders, or the portal on the Purchase Order layout and the Portal on the Purchase Requisition layout can refer to the same table and both linked by Purchase Requistion ID. With that approach, you create a new purchase order, select the Purchase Requisition ID and all the items from the Purchase Requisition portal automatically appear. That may be too simplistic--it doesn't work for multiple purchase orders from one requisition--but there are ways even that could be implemented so that your users don't have to enter all this info twice.

                        Please keep in mind that I only have the most general ideas about what you've set up. You may find that your approach works better for you than my suggestions here.

                        • 9. Re: how to get drop-down list from different table or layout?
                          a_sun

                          Hi Phil:

                          I truly appreciated for your comments.

                          I  have no idea with the script....
                          Do you mind to guide me how to write a script to copy the records from the Purchase Requisition into Purchase Orders?
                          Example script?

                          Thanks!

                          • 10. Re: how to get drop-down list from different table or layout?
                            philmodjunk

                            No script at all would be simpler. What problem do you solve by keeping two such tables of items?