6 Replies Latest reply on Jan 3, 2013 8:40 AM by Matty_1

    Drop-down menu displaying unique values

    Matty_1

      Title

      Drop-down menu displaying unique values

      Post

           I have a table that contains a variety of entries for jobs that require approval by a manager before invoicing.  My initial design for sorting out what's already been invoiced versus what's to come was simply using a button that found all the entries who's status where "ready for invoice".  This proved to slow down the process as there can be serveral entries to approve at any given time.  My solution to this probelm was to create a second table related to the first using the clients name and showing all the records that have a status set to "ready for invoice".

           As you approve the invoices they automatically get removed from the portal because I have a button that changes the status to "approved" and once the status is changed the portal no longer displays it.  This all works fine.

           Where the lack of sleep kicks in is here, I just can't seem to make it work.  What I would like to do is have a drop-down menu that shows me only clients with a "Ready for invoice" status.  Further more I'd like to see the name appear only once in the drop down regardsless of the number of job pending approval.

            

           Can someone help?

            

           Note that I have a table with records that contain all my client information.

        • 1. Re: Drop-down menu displaying unique values
          philmodjunk

               What relationship do you have between clients and invoices?

               Do you use a portal filter to select for "ready for invoice" invoices or is it built into the relationship?

               Are you familiar with creating conditional value lists?

          • 2. Re: Drop-down menu displaying unique values
            Matty_1

                  

                 

                      What relationship do you have between clients and invoices?

                 The client and invoices have they own set of relationship to link the client ID to pull the adress information.

                  

                 

                      Do you use a portal filter to select for "ready for invoice" invoices or is it built into the relationship?

                 I'm using it built into the relationship.

                  

                 

                      Are you familiar with creating conditional value lists?

                 Yes and it's what I initially tried and can't seem to make it work ...

            • 3. Re: Drop-down menu displaying unique values
              philmodjunk

                   I'm asking here for a detailed description of the relationships. What match fields do you use?

                   Basic rule of thumb: If you can set up an unfiltered portal to list the values you want in your value list, you can set up a conditional value list to list values from the same set of related records shown in the portal.

              • 4. Re: Drop-down menu displaying unique values
                Matty_1
                     

                I'm asking here for a detailed description of the relationships. What match fields do you use?

                Relationship between the jobs (Dispatch) and the approval (DispatchInvoiceApproval) table:

                Dispatch::ContactID = DispatchInvoiceApprovals::Client (Note: DispatchInvoiceApprovals::Client is a global field)

                Dispatch::InvoiceStatus = DispatchInvoiceApprovals:: ConstFRA

                      

                What I'd like is to have my "Client" field in the DispatchInvoiceApproval table display a drop down of all the clients that still have outstanding approvals but I only need to see their names come up once.  This way when there are no names in the drop down you know that everyone's been invoiced.

                      

                I tried creating a few relationships between the dispatch table and my client table and came up emtpy handed.

                • 5. Re: Drop-down menu displaying unique values
                  philmodjunk

                       I am assuming that your relaitionship is:

                  Dispatch::ContactID = DispatchInvoiceApprovals::Client AND
                  Dispatch::InvoiceStatus = DispatchInvoiceApprovals:: ConstFRA

                       A conditional value list depends on context which is established by the layout where you use it.

                       If the data in DispatchInvoiceApprovals::Client is the data you want to see in your value list, define a value list that lists values from Dispatch::ContactID and then select DispatchInvoiceApprovals as the "starting from" table occurrence.

                  I suspect, though, that you need the client's name in ;the client table. If so, add this relationship:

                  Clients::ContactID = Dispatch::ContactID

                  and then you can specify a name field from Clients as the second field in your value list.

                  • 6. Re: Drop-down menu displaying unique values
                    Matty_1

                         Thank you Phil, yes you're correct it was 

                    Dispatch::ContactID = DispatchInvoiceApprovals::Client AND
                    Dispatch::InvoiceStatus = DispatchInvoiceApprovals:: ConstFRA

                         My mistake on failing to mention that.

                         

                    If the data in DispatchInvoiceApprovals::Client is the data you want to see in your value list, define a value list that lists values fromDispatch::ContactID and then select DispatchInvoiceApprovals as the "starting from" table occurrence.

                          

                         You were close, I wanted the data from Dispatch::Client and so I created the following rleationship:

                         Dispatch::InvoicingStatus = DispatchInvoiceApprovalDROPDOWN::ConstRFA

                         and then selected DispatchIncoiingApprovalDROPDOWN as the start from table occurence.

                         For some reason I had accidently reversed the two in my earlier attempts which is of course why it didn't work.  Thanks again!