3 Replies Latest reply on Mar 16, 2015 9:30 PM by philmodjunk

    Conditional value list question

    mergatroid_1

      Title

      Conditional value list question

      Post

      Hi,

      I need to set up a conditional value list for a sales database such that when we create an estimate for a client, the credit card payment option should be limited only to the cards associated with that particular client.

      I have tables called 'Client', 'Client Credit Cards' and 'Client Credit Card Join'. All of the Client credit card associations take place in the 'Client Credit Card Join'.

      I also have related tables called 'Estimate' and 'Estimate Client Join'. The 'Estimate Client Join' table is joined to a table occurrence of the 'Client' table.

      In order to create the relationship between Estimates and Clients, I have a portal on the Estimates layout that displays info from 'Estimate Client Join'. The match field in the 'Estimate Client Join' table is a name calculation field formatted as a pop-up with a value list called 'Clients' that is populated from the 'name calculation' field in the 'Client' table. The problem that I am running into is how to associate the client's credit cards from the 'Client Credit Card Join' table. If I populate the 'Clients' value list with the client name calculation from the 'Client Credit Card Join' table, I can make the conditional card numbers work, but then I am limited to only selecting those clients with credit cards, which is unacceptable because there are multiple ways for a client to pay, so if the client doesn't have a card on file, they wouldn't show up in the 'Estimate Client Join' portal.  

      I have a hunch that I will need to join a table occurrence of 'Client Credit Card Join' to the 'Estimate Client Join' table, or some such thing, but I have been trying different configurations of this to no avail. Any insights would be greatly appreciated.

       

      alls.jpg

        • 1. Re: Conditional value list question
          philmodjunk

          I'm not sure I follow what the problem is here. Why does a relationship to limit the list of Credit cards, limit the list of clients?

          And do you need these join tables? (we can simplify a bit if you don't). You don't seem to need a join between client and credit cards unless the same credit card can be used by more than one client. (Two or more clients from the same company or family?) The lack of "crows feet" in the relationship linking credit cards to the join table suggest that this is not the case.

          And you don't appear to need a join table between estimates and clients unless you have a list of multiple clients getting the same estimate.

          • 2. Re: Conditional value list question
            mergatroid_1

            Thanks Phil - I think that I've overdone it with the joins. I'll try removing them and then have another go at it. But I think I understand your points.

            • 3. Re: Conditional value list question
              philmodjunk

              Note that with some business models, one or both join tables might very well be necessary. A credit card might be a corporate card used by different employees of the same company and you might need to treat each as a different client. A project might have more than one client--each might be a partner to be served by you both as a group and as individuals.