9 Replies Latest reply on May 7, 2014 5:19 AM by mikee7

    How can I stop duplicate entries in a portal?

    mikee7

      Hi I am working with a real mess of an Access database - 15 years old and never serviced, well actually 5 databases in total - and I am attempting to get it into a lovely and easy to use Filemaker system for a client. After a couple of weeks of effort, I am nearly there but the only way I have been able to get my data where I want it to be is to access data from two tables via one portal. This works and gives me the data feed I want but it is duplicating the results in the portal. This is probably because the portal is accessing two tables at the same time but it is the only way I can get the data I need to show. Is there a way to remove the duplicate line? Having gone from Filemaker 6 to Filemaker 13 in one step, I have a lot to learn so some help would be really appreciated.

       

      Mike

        • 1. Re: How can I stop duplicate entries in a portal?
          coherentkris

          I think it would be more helpful to post the fm file you are working on.

          • 2. Re: How can I stop duplicate entries in a portal?
            mikee7

            Hi,

             

            Many thanks for responding. Unfortunately, the database holds a great deal of confidential data which I cannot put into the public domain. I have attached screenshots of the tables used in the filemaker document and the result. The portal is taking data from Hist_2 and CODES. Codes is just that, a list of codes and ranks/positions so there is key in this table. Index in CODES relates to full code in Hist_2 so there should be a relationship there. I know it's me but the data I am working with is so messed up that it has taken me two weeks to get this point and this after 15 separate attempts is the nearest I have got to making it work!!

            • 3. Re: How can I stop duplicate entries in a portal?
              coherentkris

              and the tab control is on a layout based on what table occurence?

              What table occurence do the portals refer to?

              What fields are referenced in the edit boxes inside the portals?

              • 4. Re: How can I stop duplicate entries in a portal?
                beverly

                Understandable, mikee. We aren't asking for your DATA, just your structure.

                 

                if you cannot post file(s), post screen shots of (or type as text):

                     relationships (used for the portal)

                     portal options (sort, filter, etc.)

                     field names (including tablename:: used in the portal)

                 

                These determine WHAT is shown. Perhaps you have something not quite right in these two areas.

                 

                If you really have DUPLICATE records in any table, you may need to cull them (make some 'inactive' or something 'flagged' that can be filtered out.

                 

                Beverly

                • 5. Re: How can I stop duplicate entries in a portal?
                  erolst

                  mikee7 wrote:

                  This works and gives me the data feed I want but it is duplicating the results in the portal. This is probably because the portal is accessing two tables at the same time but it is the only way I can get the data I need to show.

                   

                  Names like Hist_2 and CODE are pretty obscure if you didn't come up with them (and the tables) yourself …

                   

                  So if your problem isn't caused by actual duplicate data, I'm assuming you're looking at something like

                   

                  someContext --< People (2 related records) --< PeopleHistory (6 records related to matching in People)

                   

                  If your portal points at PeopleHistory, you'll get a match set of 6 (history records), while you probably only want to see 2 (people records).

                   

                  The other problem of course is that you see wrong data, since a FileMaker relationship can't 'look back', only forward; so it cannot (reliably) associate the data from the People fields with the correct PeopleHistory rows.

                   

                  What you can do is create a calculation field in People that List()s the pertinent field(s) from their related PeopleHistory records into a string; then have your portal point directly at People, and display the calculation field.

                  • 6. Re: How can I stop duplicate entries in a portal?
                    mikee7

                    Hi,

                     

                    Well observed. Yes, there are many entries for one person - anything up to 6 over a time period lasting one year. I used the same table names as used in Access. I tried sorting by ascending date but the effect is limited. I will try the calculation field approach and see where it goes. Thanks so much to all of you have responded - I really, really appreciate it

                     

                    Mike

                    • 7. Re: How can I stop duplicate entries in a portal?
                      keywords

                      Mike, picking up on erolst's point about obscure naming, I suggest that you write a brief decription for yourself (if you haven't already) of each table describing what it contains and why the data belongs there. Once you have done that you could assign more meaningful names to your basic tables—and maybe even break data up differently. Then you can do the same for each relationship describing what is connected to what and why. That process can help to clarify what database is all about and what you are trying to do with it. Once you have done that I'll bet (not that I'm a gambler) that you'll need a join table or two to unravel the muddle—and maybe even to address the specific problem you've table here.

                       

                      Hope that helps a little. If not, feel free to ignore it!

                      • 8. Re: How can I stop duplicate entries in a portal?
                        erolst

                        keywords wrote:

                         

                        […[ Once you have done that I'll bet (not that I'm a gambler) that you'll need a join table or two to unravel the muddle—and maybe even to address the specific problem you've table here.

                         

                        Which is what the existence of fields 'Chair', 'Chair2' to '…6' suggests.

                         

                        And I second keyword's recommendation: bring over the data from the Access system, then try to get a clear picture of what you have, then start applying FileMaker best practices (as opposed to those from Access)  – so you …

                        mikee7 wrote:

                        get it into a lovely and easy to use Filemaker system

                        • 9. Re: How can I stop duplicate entries in a portal?
                          mikee7

                          Hi,

                          Thanks for the comments - very helpful. Sorting out the data is a nightmare. At the moment, the client uses 5 tables and 120 queries to do what I am trying to do with Filemaker so clarity is not something that is in abundance! The person who created it all and knew what went where left 10 years ago!! I may be back with more questions!

                          Mike