1 2 Previous Next 18 Replies Latest reply on Jun 5, 2013 9:17 AM by philmodjunk

    Value list conundrum

    Matty_1

      Title

      Value list conundrum

      Post

           Not sure why I can't seem to wrap my head around this.  I have three tables:  Contacts, Dispatch and RunDetails.

           The dispatch table calls upon the contact table for obvious reasons while the RunDetails contains driver information, status and other things.  The reason why I had to create a separate RunDetails table was because sometimes Dispatch jobs need to share the same status, driver etc etc.  In order to link the multiple records together you simply copy paste the RunDetailsID into the dispatch match field and you now have shared information.  When the status is changed in one record they obviously all get changed.

           What I'm looking to do is create a value list that only shows ContactIDs for dispatch records that have a status set to complete.  Remember that the status is stored in the third table which is RunDetails.  Seems like this should be easy but I can't wrap my head around this, I've tried several serried relationships and can't ever nail it.

            

           PLEASE HELP!

        • 1. Re: Value list conundrum
          philmodjunk

               Can you spell out the details of your relationships?

               Do you know how to use a relationship to create a conditional value list?

          • 2. Re: Value list conundrum
            Matty_1

                 Yes I certainly do, I've done several conditional value lists but what's confusing me is having the status in a third table.  If it was between two tables it would be easy for me.

                 The relationships are as follows:

                 Dispatch::RunDetailsID MATCH FIELD = RunDetails::RunDetailsID

                 Dispatch::ContactsID MATCH FIELD = Contacts::ContactsID

                 I also have a field called cComplete with a calculation equal to "Complete" in all three table that I've tried to put in a series (in various combinations) to achieve the proper results with no success. 

            • 3. Re: Value list conundrum
              Matty_1

                   Good morning Phil, have I provided you with enough information to paint a proper picture?

              • 4. Re: Value list conundrum
                philmodjunk

                     A sketch of your relationships:

                     Contacts------<Dispatch>-----RunDetails

                     Which makes Dispatch a join table between Contacts and RunDetails.

                     

                          What I'm looking to do is create a value list that only shows ContactIDs for dispatch records that have a status set to complete.  Remember that the status is stored in the third table which is RunDetails.

                     So it would appear that you are trying to select a ContactID in Dispatch, but only if they have a RunDetails record set to complete. Seems like there's another, unstated criterion involved here that would further limit this to only certain records in RunDetails having a complete status value.

                     Do you want the value to appear in the list if any rundetails record has that ContactID and a value of "Complete" in the status field?

                     What about a new contact that has never been dispatched to before?

                • 5. Re: Value list conundrum
                  Matty_1

                       There is a fourth table where I want to use the list but I never mentioned it because I didn't think that mattered.  The ultimate reason for this is the following.

                       I want to allow the users to have a look at a drop down and see if there are any outstanding jobs left for invoicing.  I first wanted to get the list functioning showing all completed jobs and then I would add in another relationship that would remove any that have been invoiced.  This way by simply clicking on the drop down menu you quickly find out if there are outstanding invoices and removes the possibility of having some work slip through the cracks.  This also removes the extra step of performing finds to come up with the same results.  Once a client who's name appears in the list is selected, there is a portal that displayed all outstanding dispatch jobs that need invoicing.

                       Currently the Rundetails does not contain the client ID (that would be the easiest solution) but it can't because the RunDetails can span between multiple contacts depending on where we pick up and drop off.  The same RunDetail ID could be linked to a long list of jobs and clients.  The match field linking the rundetails to dispatch is contained in the dispatch table to allow copy pasting if you want to link any dispatch job from one to the other.

                       

                  Do you want the value to appear in the list if any rundetails record has that ContactID and a value of "Complete" in the status field?

                  Almost, I want the contact to show in the list when a dispatch record has a status set to complete but the kicker is that the status is stored in the third table RunDetails.

                       

                  What about a new contact that has never been dispatched to before?

                  Those will not matter as I'm simply looking for a list of outstanding records needing to be invoiced.

                        

                  I'm not the best at providing all the appropriate details so please let me know if you require any more information.

                  • 6. Re: Value list conundrum
                    philmodjunk
                         

                              There is a fourth table where I want to use the list but I never mentioned it because I didn't think that mattered.

                         It matters as a specific relationship between that table and the others may be needed for your value list.

                         With a script performed from an OnObjectModify or OnObjectSave trigger on Run Details, you can set a field in Dispatch to complete and then this field can be properly indexed and used as part of a relationship. Make sure that your script has sufficient flexibility to both set and unset the value.

                    • 7. Re: Value list conundrum
                      Matty_1

                           I was under the impession that a value list performed the same regarless of which table it is placed and functioned stricly on it's relationship settings?

                           IE:  If you ask a value list to list off all the contact ID it will list then even if the table you're on is in no way linked to the contacts table.

                            

                           Is that not the case?

                      • 8. Re: Value list conundrum
                        philmodjunk

                             This is not the case if it is a conditional value list. A conditional value list is controlled by a relationship and thus the table occurrence context can play a crucial role in what values actually appear in the value list.

                        • 9. Re: Value list conundrum
                          Matty_1

                                

                               Ok with that being said I went back to the drawing board.  I'm almost able to get it working the way I want it to but for some reason when I add the most important relationship it breaks it so clearly I'm missing something.  Please have a look at the image attached to see my relationship, but first I will explain my setup in further detail.
                                
                               I have the following tables that are implicated in this relationship.  By trying this new relationship I realized I don't need the contacts table.
                                
                               DispatchInvoicingApproval, DispatchJobs, DispatchCentral, RunDetails
                                
                          DispatchInvoicingApproval is a table with one record, the one Record has a global field and a portal.  When the global field is set to a particular ContactID, the portal reveals all the outstanding items up for invoicing approval of that one particular client.  The records being approved are from the DispatchJobs table, these contain all the invoicing and logistical data required for our clients and the relationship is setup to only show records that have an empty invoicing match field.  It is in this layout/table that I want to put the drop down field that contains only ContactIDs who's DispatchCentral records have a status set to complete.
                                
                          DispatchCentral is the backbone of the dispatching system and allows us to link to records from another part of our database.  Linked to this table are RunDetails and DispatchJobs.  The RunDetails table contains the status among other information and needs to be in a separate table because I need to have the ability to link it to other DispatchCentral records so they can share the same status and load information.  The relationship is established through the RunDetailsID  (DipspatchCentral::RunDetailsID = RunDetails_DispatchCentral::RunDetailsID).  Also linked to these records are DispatchJobs, these records show a breakdown of each individual task associated to the one DispatchCentral entry and again is linked using the DispatchCentralID  (DispatchJobs::DispatchCentralID MATCH FIELD = DispatchCentral_DispatchJobs::DispatchCentralID)
                                
                               Naming convention in the image has the table name and other table down the series get abbreviated in order of the relationship.
                                
                               With the attached image I was able to have the drop down show me all the DispatchCentral entries that are set to "Complete" via DispatchJobs_DC_RD_DIA::DispatchCentralID MATCH FIELD = DispatchCentral_DJ_DC_RD_DIA::DispatchCentralID.
                                
                               I want to add an additional relationship that will remove any record that has already been invoiced.  I thought the following would work but for some reason it breaks the drop down.  Here's what I did, let me know what you think.
                                
                          DispatchJobs_DC_RD_DIA::DispatchInvoiceNo MATCH FIELD = DispatchCentral_DJ_DC_RD_DIA::cIsEmpty.
                          • 10. Re: Value list conundrum
                            Matty_1

                                 Note that cIsEmpty is simply a numbered field that has remained blank, no data has ever been enetered in it.

                            • 11. Re: Value list conundrum
                              philmodjunk

                                   What is the exact calculation expression for cIsEmpty?

                                   If it's just IsEmpty(table::Field),

                                   then it can only returns the values 1, 0 or null. Don't see how that can possibly match to a value in DispatchJobs_DC_RD_DIA::DispatchInvoiceNo MATCH FIELD--which appears to store the ID numer for an invoice record.

                              • 12. Re: Value list conundrum
                                Matty_1

                                     cIsEmpty is simply a numbered field, no calculations, just empty which I thought would match it up to all the InvoiceNo fields that are empty there removing from the relationship any records that have already been invoiced.

                                • 13. Re: Value list conundrum
                                  Matty_1

                                       How would you suggest I build into my relationship a way to only show records that have not been invoiced?  As it stands, the match field DispatchInvoicingNo MATCH FIELD is the only way to know that the record hasn't been invoiced by finding the empty ones.

                                  • 14. Re: Value list conundrum
                                    philmodjunk

                                         As this has demonstarted, you can't match records using null values (empty) in fields. Your screen shot of relationships don't show me any table occurrence for invoices so I am unable to be specific but you'll need a field that has data when you don't have a related record in invoices.

                                    1 2 Previous Next