6 Replies Latest reply on Jul 6, 2009 10:03 AM by TSGal

    Redundant Data Created.... Why? and how can I eliminate it?

    synergy46

      Title

      Redundant Data Created.... Why? and how can I eliminate it?

      Post

      Here is the setup:  Click here

        • 1. Re: Redundant Data Created.... Why? and how can I eliminate it?
          TSGal

          synergy46:

           

          I'm not sure where the redundant data is made.  Where are you adding the Option data?  If you enter it from the OptionTransactions table, the optionid.fk should look up the optionid.pk and provide any information you need from the Option table.  If you are doing it from the Option table, then whatever you enter in the optionid.pk file should look up the information in the optionid.fk field in the OptionTransactions table and present the data.

           

          When you set up a portal, do not put the key/link field in the portal.  Since you are already connected via the portal, if you change that field, the record will disappear from the portal.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Redundant Data Created.... Why? and how can I eliminate it?
            synergy46
              

            Thank you for the reply.

             


            TSGal wrote:

            synergy46:

             

            I'm not sure where the redundant data is made.  Where are you adding the Option data?  If you enter it from the OptionTransactions table, the optionid.fk should look up the optionid.pk and provide any information you need from the Option table.  If you are doing it from the Option table, then whatever you enter in the optionid.pk file should look up the information in the optionid.fk field in the OptionTransactions table and present the data.

             

            When you set up a portal, do not put the key/link field in the portal.  Since you are already connected via the portal, if you change that field, the record will disappear from the portal.

             

            TSGal

            FileMaker, Inc. 


            The redundant data is in the OPTIONS table. When I add  new record to OptionTransactions, it adds a new record in OPTIONS.  Why?  I have the same arrangement in Stocks and it works perfectly. 

             

            The data is added via a layout based on STOCK in a tab with a portal based on OPTIONTRANSACTIONS.  In that layout I have OPTIONTRANSACTIONS::OptionID.fk.  OptionID.fk is a value list based on OPTIONS::optionID.pk and a second field Options::optsymbol.  I really want the optsymbol to be inserted in the OPTIONTRANSACTIONS::optionID.fk field. 

             

            When I make an entry via the popup valuelist the first value to appear is the OPTIONS::optonid.pk. If I click the same field again, the value to appear is OPTIONS::optsymbol????  This inconsistent behavior is perplexing but probably symptomatic of something important I don't "GET".

             

            I use this so that the user can click the optiontransactions::optionid.fk and see all the options related to the current stock.  This works great.   The workings of the insertion into optiontransactions::optionid.fk is the perplexing part.

             


            • 3. Re: Redundant Data Created.... Why? and how can I eliminate it?
              TSGal

              synergy:

               

              Thank you for the clarification.

               

              Here is what I did:

               

              1. I created an OptionsTransactions table with the following fields:

               

              optionid.fk

              optionTrans.pk

              contracts

              TransactionType

              strike

              expdate

              expenses

              premium

              transdate

              net

               

              2. I created an Options table with the following fields:

               

              optionid.pk

              stockid.fk

              optsymbol

               

              3. I entered the following information into Options:

               

              optionid.pk - optsymbol

              21 - aapl.aa

              22 - bac.aa

              23 - aapl.bb

              24 - wfc.aa

              25 - wfc.bb

               

              4. I created a relationship between Options and OptionTransactions using OptionID.pk and OptionID.fk, respectively.

               

              5. In OptionTransactions, I created a portal into Options, displaying the Optsymbol field.

               

              6. Using your example - "add a new record to OptionTransactions", I can enter the OptionID.fk field, and the correct optsymbol appears in the portal.  No new record is added.  For example, if I enter 22 into OptionID.fk, "bac.aa" appears in the portal.

               

              =========

               

              7. Since you are now talking about STOCK table, I created the STOCK table with the following fields:

               

              stockid.pk

              symbl

              stkname

              recno

               

              8. I then created a relationship between STOCK and OPTIONS using the stockid.pk and stockid.fk fields, respectively.

               

              9. The relationship between Options and OptionTransactions was modified so you can add new records to OptionTransactions.

               

              10. Based on the information in the Options table, the records were modified to enter the stockid.fk field:

               

              optionid.pk - optsymbol - stockid.fk

              21 - aapl.aa - 1

              22 - bac.aa - 2

              23 - aapl.bb -1

              24 - wfc.aa - 3

              25 - wfc.bb - 3

               

              11. In the Stock table, I entered the following information:

               

              stockid.pk - symbl - stkname

              1 - AAPL - Apple Computer

              2 - BAC - Bank of America

              3 - WFC - Unknown

               

              12. In order to add an OptionTransaction, you need to select an Option.  Therefore, from your Stock table, you need to find an Option before entering an OptionTransaction.  In your Options table, create a portal into your OptionTransaction table, and also a portal into your Stock table (or just a pop-up to select the Stock).

               

              A stock can have several options, and each option can have several option transactions.  That is the reason you can't add an option transaction until the option is selected.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Redundant Data Created.... Why? and how can I eliminate it?
                synergy46
                   You understand the project pefectly.


                 

                A stock can have several options, and each option can have several option transactions.  That is the reason you can't add an option transaction until the option is selected.

                 

                >>Right.  When I select the OptionTransactions::OptionID.fk popup and am presented with the fields  from OPTIONS:: , a new record is created  in OPTIONS:: containing a new OptionID.pk and the selected StockSymbol.  Also, the Options::OptionID.pk is inserted into the OptionTransactions::OPtionID.fk field?  At this point, the new Options::OptionID.pk is inserted into the new Options row AND the same value is inserted in a new record in OptionTransactions. 

                 

                If I click the SAME field again and select the same Option, Appl.AA for example, the value in the OptionTransactions::OptionID.fk CHANGES to the Optioon::OptionID.pk where the Options::optsymbol actually matches.

                 

                 

                For example, when I first click the OptionTransactions::OptionID.fk field and select the AAPL.BB option, 60 is entered in the OptionTransactions::OptionID.FK and Options fields, as above.  But, if I select the same option a 2nd time, the value in OptionTransactions::optionID.fk changes appropriately to 55.  And, I am left with the newly created row with a Options::optionID.pk 60 ???? 

                TSGal

                FileMaker, Inc. 


                 


                • 5. Re: Redundant Data Created.... Why? and how can I eliminate it?
                  synergy46
                    

                  TSGal wrote:

                  synergy:

                   


                  6. Using your example - "add a new record to OptionTransactions", I can enter the OptionID.fk field, and the correct optsymbol appears in the portal.  No new record is added.  For example, if I enter 22 into OptionID.fk, "bac.aa" appears in the portal.

                   

                  >>I would really like to see how you did that!!!  I not only don't get the 'bac.aa' entered in optionid.fk, i get the options--optionid.pk of a NEW RECORD instead... yikes...

                   

                  >>To summarize, you have optiontransactions portal based on a STOCK layout with optiontransactions--optionid.fk hooked to a optionid.pk and optsymbol in OPTIONS???? 

                   

                  =========

                   

                  7. Since you are now talking about STOCK table, I created the STOCK table with the following fields:

                   

                  stockid.pk

                  symbl

                  stkname

                  recno

                   

                  8. I then created a relationship between STOCK and OPTIONS using the stockid.pk and stockid.fk fields, respectively.

                   

                  9. The relationship between Options and OptionTransactions was modified so you can add new records to OptionTransactions.

                   

                  10. Based on the information in the Options table, the records were modified to enter the stockid.fk field:

                   

                  optionid.pk - optsymbol - stockid.fk

                  21 - aapl.aa - 1

                  22 - bac.aa - 2

                  23 - aapl.bb -1

                  24 - wfc.aa - 3

                  25 - wfc.bb - 3

                   

                  11. In the Stock table, I entered the following information:

                   

                  stockid.pk - symbl - stkname

                  1 - AAPL - Apple Computer

                  2 - BAC - Bank of America

                  3 - WFC - Unknown

                   

                  12. In order to add an OptionTransaction, you need to select an Option.  Therefore, from your Stock table, you need to find an Option before entering an OptionTransaction.  In your Options table, create a portal into your OptionTransaction table, and also a portal into your Stock table (or just a pop-up to select the Stock).

                   

                  >> I don't 'get' #12.  Stocks are added to the stocks table before any options are entered in optiontransactions.  Also, you say " from your Stock table, you need to find an Option before entering an OptionTransaction."  There are NO OPTIONS in the STOCKS table.  Options are in the OPTIONS table.  ok?

                   

                  A stock can have several options, and each option can have several option transactions.  That is the reason you can't add an option transaction until the option is selected.

                   

                  >>Right, with a particular stock selected, Apple for example shows above the portal, you should be able to select all the Apple related options in optiontransactions--optionid.fk value list.   But, what do you mean by "...you can't add an option transactions until the option is selected?"  Are you talking about the optionid.fk value list selection?  

                   

                  I am really at a loss on this one. I have seemingly tried every combinatin of layout TO, portal TO, Lookup, Value List, Field basis and anything else I can think of.  I have scoured the web looking for answers.  I have referenced 4 FM books and can't get a clue.  I hope you can set my thinking straight.

                   

                  Thanks

                   

                  And, there is the weird problem of a blank row being added in OPTIONS with every optionid.fk selection in OptionTransactions...??????

                  TSGal

                  FileMaker, Inc. 


                   


                  • 6. Re: Redundant Data Created.... Why? and how can I eliminate it?
                    TSGal

                    synergy46:

                     

                    Since my previous posting, I sent you a replacement file that has a tab for Options and OptionsTransactions.  Either the first or second file should get you pointed in the right direction.

                     

                    TSGal

                    FileMaker, Inc.