4 Replies Latest reply on Aug 22, 2010 7:05 PM by basilisk2

    Do Many to Many link tables work in Portals?



      Do Many to Many link tables work in Portals?


      I've been wrestling with a Portal for the last three days which isn't working properly. To begin with I thought it was because two of the fields were using Conditional Value Lists, but those fields work fine; it's the fields that come from the linking table in a Many to Many join that only show a single record of data, no matter how many are entered.

      I've saved the FMPA11 file here:


      The problem is in the FundTestCopy layout which is based on Table:dClients which contributes one field <dClients.Client Name> to the main screen. The Portal is based on Table:dFunds.

      The first Portal field is <dFunds:FundCo> and uses a Popup menu based on Value List AllFundCos which looks up the data in Field <dAllFunds.FundCo> set to "Include All Values".

      The second Portal Field is <dFunds.FundName> and uses a Popup menu based on Value List AllFundNames which looks up the data in <dAllFunds.FundNames> set to "Include only related values starting from - dFunds".

      The third Portal Field is <rHoldings.Quantity> which is an Edit box.

      The fourth Portal Field is <rHoldings.Value> which is an edit box.

      The Conditional Value Lists work great, but the fields from rHoldings seem only to record one set of data. When you enter the data for the second record it changes the data in the first record to be the same as the second record. Enter a third set of data, and this data then becomes the data for all three records.

      Where am I going wrong? This might be a more complicated construction than the one in the Knowledge Base, but ideally I'd like it more complex still, so that the Value field is a calculated field taking info from other tables yet to be added. I'd settle for this set up working first of all though...

      I posted this first here:


      but I realised that I was asking the wrong questions there (about Conditional Value Lists) when the problem was more likely a Portal/Relationship issue, hence this reposting.

        • 1. Re: Do Many to Many link tables work in Portals?

          Your portal should be based upon RHoldings.  You should have the rHoldings::FundID displayed and your second value list should be attached to this field.  The value list should be using FundID as first value and then also display second field of fund name.  You can then, in your value list, only show values from second field.

          I have no idea why you even have that AllFunds table.  You only need one table for fund companies and one table for funds.

          UPDATE:  You should use all values from Fund Company.  And you should have a global to hold the Fund Company.  Then your filtered relationship (for the conditional value list) of FundIDs would be 'only related values' based upon this relationship.  As it is, when you select a Fund Company in the portal, you are actually changing the Fund Company itself.

          • 2. Re: Do Many to Many link tables work in Portals?

            Thanks for your help. I am using dAllFunds to try and recreate the KB Conditional Value List example which uses two identical tables:


            How will showing the ID numbers from the FundID field allow users to a) see the Fund Company and Fund Name and b) allow the CVL to work? Can you explain it a little more please?


            • 3. Re: Do Many to Many link tables work in Portals?

              Oh. No wonder you were having difficulty.  I've revised your file quite drastically although I do not believe I've changed the underlying business logic.  See if this is what you are looking for.


              Note that the dFunds 2 table is another occurrence (an additional representation) of the dFunds table  - not a different table.

              • 4. Re: Do Many to Many link tables work in Portals?

                Thanks LaRetta, cavalry as always! :) Your result is what I was looking for myself, but I got confused between CVL and Portals and relationships, and I really wasn't clear what duplicate tables were for. I got the CVL to work, but applying that skill eluded me. Why does the duplicate table scenario work?

                Also, will the Many to Many relationship between dClients and dFunds that travelled through rTransfer still work? That is one of the core areas of my database. The rTransfer table was deleted by accident when I was putting the eg together for help, and the dFunds table was being used for a different purpose. Will it still be possible to link this separately to the dClients table without breaking the logic? I only left it in in case the coincidence of having the same Field name as another table was the cause of the problem... this is how it linked up before:

                dClients (ClientID) --< dTransfers (ClientID, FundCo) >-- dFundCo (FundCo)

                I think your changes may affect the way rTransfers worked, but I do not fully understand yet how your solution will affect dFunds... but I do agree having only one Table for FundCo/Name is best practice.

                Can you please shed any light on these things?