10 Replies Latest reply on Mar 29, 2010 10:10 AM by PierceButler

    weird relationship glitch



      weird relationship glitch


      In TableA, I have a calc field, which I'll call "Answer" here, with an output of either 1 or 0.


      In a directly related TableB, I have a calc field, which I'll call "Query", whose formula is TableA::Answer.


      Searching for Answer = 1 in TableA, I find 1,913 records. Searching for Query = 1 in TableB, I find 106. But, all the data in TableA came from TableB, and no records in TableB have been deleted.


      Yet every record in TableA where Answer = 1 finds a value (correctly, according to spot check) from an independent field in TableB. Though the TableB/TableA relationship is respectively one/many, neither eyeball nor self-join relationship finds any duplicate values in the link fields (a straightforward ID number) on either side.


      It's possible to calculate the value of Answer from data in TableB, and from that to find the 1,807 TableB records in which Query = 0 even while a portal to TableA shows Answer = 1 (this is consistent when I change the Query formula to TableA::Answer = 1). I can't see any difference between the TableB records where Query gives the "right" result and those where it doesn't.


      For the present I'm able to find the results I need, but some of the necessary data inTableB is temporary and eventually I won't have that option. Meanwhile the anomaly is driving me nuts.


      I hope this is clear: probably I've left something important out of the problem description here, which I expect is something crucial to the problem itself.


      Thanks in advance for all comments & suggestions!


      FTR: using FMP Adv 11.0v1 on a standalone MBP running MacOS 10.5.8; have used FM since version 3.

        • 1. Re: weird relationship glitch

          How are the two tables related? 



          • 2. Re: weird relationship glitch

            Each record in TableB has an (arbitrary) ID number (treated as text, since it's not used for any numeric purpose).


            That number is also used in a field of each record in TableA, and linked to its TableB equivalent.

            • 3. Re: weird relationship glitch

              Can you explain your structure with more realistic terms rather than abstract?

              • 4. Re: weird relationship glitch

                I'm working on local political campaigns, using data from the county Elections office.


                To simplify: each record received includes a voter's ID number, name, address, etc, and details on which elections they have voted in.



                12345678   Doe, Jane  1919 2nd St   Y A S ... (meaning Yes, Absentee, Skipped voting in elections X, Y, & Z respectively)


                So I have to take this flat file and break it up into a relational format. The 1st part goes into TableB ("Voters")

                [12345678   Doe, Jane  1919 2nd St ... ];

                the rest goes into TableA ("Votes")

                [12345678   X   Y

                 12345678   Y   A

                 12345678   Z   S



                In the Votes table, I set up a calc field ("Answer") that gives a "1" answer if the election is X and the vote is any positive value (Yes, Absentee, etc).


                The Voters table has a field ("Query") that should give a "1" if it finds a related Answer = 1.


                My problem is that Query only shows about 1/19 of the hits it should. So either I am making some ridiculous screwup, or FMP is. I hope/bet it's me.


                I'm getting around this at present because I haven't deleted the original flat file data. The next campaign, I'll get new info on the same voter, and will have to rely on the related table when looking to see who turned out for Election X.

                • 5. Re: weird relationship glitch


                  Pierce Butler wrote:

                  In the Votes table, I set up a calc field ("Answer") that gives a "1" answer if the election is X and the vote is any positive value (Yes, Absentee, etc).



                  This part still doesnt make much sense to me. You shouldnt have a specific election type in the calc field because the you would have to create a calc field every year.


                  Instead if you have a generic calc field for Answer for those statuses that are considered as voted then this is a rather easy find.


                  Your calc for Answer should be something like:


                  status  = "S" OR status = "A"


                  This will then flag 1 if true.


                  Now from the Voters layout, put a copy of the vote year field on the layout as well as the Answer field. You may want to consider putting a portal on the layout to see all the voting history.


                  Go into find mode and put in the note year and 1 in the Answer field. It should only result in a found set of voters that has 1 in the Answer field for that year you selected.






                  • 6. Re: weird relationship glitch

                    This "doesn't make much sense" to you because I simplified what I've got to describe it here.


                    In fact, I have implemented something very much like your approach (though it bears noting that we have more than one election per year to track, so each is also given a unique ID number, abbreviated above as "X, Y, Z...").


                    The candidate asked me to produce a file listing voters who had turned out in one particular race (when your campaign is a run-off for a municipal election, you need to concentrate on the small percentage who show up in similar contexts). I had thought setting up my "Query" and "Answer" fields was a quick & easy way to do that.


                    There is a much more elaborate setup to process the Votes table for statistical purposes, but it seems to work. My ad-hoc solution doesn't work, and I'm still concerned as to why. F'rinstance, I created another calc field in the Votes table to display each voter's precinct, and spot-check indicates that it does so, correctly, with no failures - so the link seems functional one way, but not the other.

                    • 7. Re: weird relationship glitch

                      It continues to not make sense to me because I do not understand what you are trying to do. Forget the predetermined methods at this point. What is it you are trying to get?


                      As for the year, that is irrelavant. I just assumed for the sake of the example but it should matter whether there was 1 election or 20 elections for a particular year.

                      • 8. Re: weird relationship glitch

                        What I'm trying to get is why a calc field whose output is the content of a related field only shows the right answer in a small percentage of cases.


                        If it came out wrong _every_ time, I'd be sure it was my error in setting up the formula.


                        Likewise if it came out with the same answer in all cases, whether that answer was wrong or right.


                        Instead, it shows the correct answer only 1/19th of the time. More specifically, of the "positive" results in one table (the ones that have what I'm looking for), 18/19ths are false negatives in the related table. (Apparently, I'm getting zero false positives.)


                        What I want is a reliable way to pull out a list of the voters who voted in one of the 75 (so far) elections for which I have data. I set up an ad-hoc field to identify the instances I want in the Votes table, and another ad-hoc field to flag those persons in the Voters table - not quite the way you outlined, but similarly. So we seem to agree on the strategy, but in this situation something's gone off the rails. I can work around the bug, and have, but it's time to go back and identify what's out of whack for the sake of future projects.


                        If I can't figure out what's going wrong here, when I'm able to cross-check it, how can I have any confidence in other cases when I won't have that option?


                        Though we don't seem to be on quite the same frequency here, I'm very grateful for your interest in helping. Would it assist you if I were to try describing what I've got here in even greater detail - exact copies of formulae, etc? (I think it would be illegal to send you a copy of the file, even if this forum were set up to allow that, since it contains voters' personal information which I had to sign a non-disclosure agreement to receive.)

                        • 9. Re: weird relationship glitch

                          You can always send me an email with a CLONED copy of the file which will contain no data. I would not mind helping you out backchannel. Send me a Private Message and I will give you my email address.

                          • 10. Re: weird relationship glitch

                            Thanks much for your kind offer.


                            An empty clone, without records, might not duplicate the glitch when you put new data into it.


                            I'll have to prepare a version with false records that does replicate the problem, which may take a little while. Pls excuse the fact that, as a long-term work-in-progress, there will be numerous loose ends and uncleaned-up messes in the package.


                            Pvt Msg coming asap.