6 Replies Latest reply on Jun 4, 2009 12:30 AM by david583

    select specific item from a portal

    david583

      Title

      select specific item from a portal

      Post

      I have a customers table and an equipment table. 1 customer has many equipment.

      I have a report that lists all customers and I'm trying to show only "Olympus E-1 Camera" from equipment in a portal.

      I have managed to sort the portal so that the camera is at the top and is the one displayed, however if no camera is with that customer it will show whatever is in the top of the list.

      How can I do this so that it gives me a blank return if no camera is present? I have tried many ways and so far the portal method gives the best result except for these other items.

        • 1. Re: select specific item from a portal
          TSGal

          david583:

           

          Thank you for your post.

           

          If you are trying to only show "Olympus E-1 Camera" in the portal, then make sure the relationship includes "Olympus E-1 Camera".  It sounds like it is showing all equipment.

           

          If you also want to show all equipment, then add a second table occurrence and build the relationship based on "Olympus E-1 Camera".  Then, the portal will only show that entry and not the other equipment.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: select specific item from a portal
            david583
              

            Thanks TSgal for the quick reply.

            I'm not sure of how to do what you say though."OLYMPUS E-1 CAMERA" is an item in my description field of Equipment table. the table is related by a customer id, so we can move equipment between Customers. I'm not sure how to include the camera in the relationship as there are many other descriptions.

             We are trying to find out which camera each customer has by serial number, and if there is no camera there we want to reflect that by a blank or text message against that customer.

            i.e. <customer id><customer name><equip. description(camera only)><equip. serial number(camera only)>  (id & name from customers table, description and serial number from related equipment table. Relationship via customer id.)

            The above line appears for each customer in the report. I have sorted records by"OLYMPUS E-1 CAMERA" and if there is a camera it works fine, however if no camera is at that customer it picks the next equipment description and serial number available.

             

            Should I set up a new relationship? how can I isolate the camera in this relationship from other items in the description field. 

            I have also tried creating a lookup field in the customers table to get the camera serial number with little success. Both lookup and calculation auto entry seemed to have difficulty, but that may be in my syntax or wrong function use.

            • 3. Re: select specific item from a portal
              comment_1
                

              Yes, you should set up another relationship, using a new occurence of the Equipment table. Something like:

               

              Customers:: CustomerID = Equipment 2:: CustomerID

              AND

              Customers:: gDescription = Equipment 2:: Description

               

              If you enter a description (e.g. "OLYMPUS E-1 CAMERA") into the global gDescription field, the relationship will show only equipment that matches the description.

               

              • 4. Re: select specific item from a portal
                philmodjunk
                  

                You've spelled out a lot of complications in your design that make it difficult to suggest improvements. You could put a calculated ID field in your equipment table that works something like:

                 

                If(patternCount(descriptionfield, "camera") > 0, customerIdfield, "")

                 

                Use that field as your key field in your relationship and only records that contain "camera" in the description field will appear in your portal.

                 

                Given the other information in your post, it may be that a significant redesign of your database tables and relationship might be in order.

                • 5. Re: select specific item from a portal
                  david583
                    

                  Thanks PhilModJunk,

                  Brilliant, works a treat.

                  I agree my DB is a bit convoluted but I'm not a developer and my company wont pay for one so I'm struggling through. I am planning a clean up with a second version once I have my relationships right. thanks for your help, and you'll no doubt hear from me again.

                  • 6. Re: select specific item from a portal
                    david583
                       yes thanks that does work, but I think I'll do it the way PhilModJunk suggested. i appreciate your help.