10 Replies Latest reply on May 30, 2012 12:50 PM by philmodjunk

    Need to build drop-down list based on query of past records



      Need to build drop-down list based on query of past records


      We serve the homeless. A client can request many resources, but some resources are limited on how often you can get that item. For example, you can only receive shoes or a blanket once every month. 

      A contact table -> interaction table -> resource requests table -> resources table

      When a client visits today (interaction) he may request certain resources. We want to limit the resources he can request based on his previous history. 


      Also, how do we keep a client from requesting the same resource twice on the same interaction?

        • 1. Re: Need to build drop-down list based on query of past records

          Do you have many request records related to a single interaction record? (If client comes in and requests 5 resources, you add one interaction record and 5 request records linked to it?)

          If a client requests a resource and doesn't get it, is the request still logged in your database? It's not clear how one can tell what resources have been received and when as opposed to when those requests were made but not fulfilled.

          My best guess is that you want to prevent the client from making a request if they are not permitted that resource. Does availability also affect this? (We'd give you shoes, but don't have any on hand that fit you...)

          What you describe is a conditional value list with a few extra wrinkles such as making the value list "diminish" with each new request on the current interaction record. The value list can use a relationship to filter down the available resources to those permitted for the current client--but as always--the devil is in the details...

          • 2. Re: Need to build drop-down list based on query of past records

            A contact created an "interaction" when they visit or call us. We take notes on the interaction and they often request resources - which creates records in a table called requested resource. Those resources are listed in a "resources" table. 

            When resources are requested, the resource is chosen and there are also fields for "was fulfilled" and "date fulfilled". We need to track what resource were fulfilled, when they were fulfilled and what resources were requested - but never fulfilled. Yes, we track requested that are NOT fulfilled. This helps us track what donation we need. (Shoes, pants, etc...)

            • 3. Re: Need to build drop-down list based on query of past records

              I'll take that as a "yes" to my initial question...

              You never answered whether a resource's current availability should be reflected in the drop down, so I will ignore that possibility for now.

              Where/how do you record how frequently a resource can be distributed to the same client? I'm assuming for the moment that your Resources table has one record for each type of resource. (One record for shoes, one for blankets, etc) and that a field in this table indicate the frequency with which the resource can be given out.

              Hmmm, as I think about that detail--each resource may have it's own distribution frequency--I realize that this will add significant complexity to the relationships need to pull this off. So please confirm that this is the case before I move forward on that idea.

              Also, does this have to be a drop down list? A filtered portal may provide a workable alternative where the items listed in the portal can be clicked to request a resource and the filter on the portal can help eliminate resources not permitted for the current client.

              • 4. Re: Need to build drop-down list based on query of past records

                Yes - that is corect.(to your initial question)

                Yes - we would like the availability of the resource to be reflected in the drop down. 

                Yes - The resource table lists all of the available resources (one record for each Pants, Tshirt, Socks, Shoes, Hygiene Kit, Blanket, etc...)In the resources table, the records also have a field labeled "Days Between" which determines how many days between fulfillment. 

                I would prefer the filtered portal list. If a resource were not available it would be great if it would list the day it was last fulfilled (and the checkbox would be disabled so it can't be selected). Some items are also gender specific. There is a field in the resources table for gender. If it is specified, you could also limit by gender. (for example, men can't request feminine hygiene products, etc...)


                Would it help if I placed the database that I am working on online so you could download it?



                • 5. Re: Need to build drop-down list based on query of past records
                   The idea of a portal with all of the resources so the user can selectively choose which ones - sounds perfect.
                  • 6. Re: Need to build drop-down list based on query of past records

                    Sorry that I haven't gotten back to you sooner. It's been a long weekend with zero time for participating in the forum.

                    This isn't a simple issue as you have multiple factors that can control what items are available for a given client. Resource availability is something I'm going to set aside for the moment as that brings up a new set issues in terms of inventory management. Let's see if we can get the gender and time interval factors working first. Either a portal or a conditional value list can work for this, but portal filtering provides options for more flexible ways to limit which resource records are displayed and items currently not available to the current client can still be listed but formatted to show that they are not available.

                    Then we can make the entire portal row or just a button on the row something that the user can click to select that resource for the client. The script performed when you click to select a resource can either do nothing or display an error message if the selected resource is not available for the current client.

                    We need a new occurrence of ResourceRequests so that we can set up a new relationship that matches by ResourceID, ClientID and CurrentDate

                    If the values match by the first two values and the CurrentDate falls in the interval for when the client may next receive that resource, we know that the client may not receive that resource item.

                    But first, let's make sure that your relationships are set up the way that I think they are. I may have different names here, but you should see relationships like this in Manage | database | Relationships:

                    Clients::clientID = Interaction::ClientID

                    Interaction::InteractionID = ResourceRequests::InteractionID

                    ResourceRequests::ResourceID = Resources::ResourceID

                    This produces: Clients----<Interaction----<ResourceRequests>----Resources   (---< means one to many)

                    in Manage | database | relationships

                    • 8. Re: Need to build drop-down list based on query of past records

                      That's the idea, but you need to take a closer look at the CONTACT ID MATCH... field in Interactions. I see ----| in the relationship line where I should see ----<.

                      What I see suggests a global field or unstored calcualtion field. Either will not work here. It should be a data field with the same type as the oddly named Contacts::Contact ID Matching Field in contacts but without the auto entered serial number setting. (And I see where it's from--the Contacts starter solution--but I find the field name odd just the same.)

                      First thing we'll need is a new occurrence of ResourcesRequests:

                      In Manage | Database | relationships, make a new table occurrence of ResourcesRequests by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ResourceRequestByClientDteRsrc.

                      We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                      I've run out of time for today, see if you can get those two things done and I'll check back tomorrow.

                      • 9. Re: Need to build drop-down list based on query of past records

                        I think I have completed those two steps

                        • 10. Re: Need to build drop-down list based on query of past records

                          Define this calculation field, cInteractionIDList, in Contacts:

                          List ( Interactions::InteractionID )

                          This will produce a return separated list of the interactionID values for all interaction records created for this client.

                          Define an unstored calculation field, cToday, in Resources as:

                          Get ( CurrentDate )

                          Define a global text field, gInteractionIDList, in Resources.

                          Define a date field, ReceiveAgainAfter and give it this auto-entered calculation:

                          If ( Not IsEmpty ( Resource Given Date ) ; Resource Given Date + Resources::Resource Days Between )

                          This field will be blank for all existing resource Request records. To update the existing records, copy this expression to your clip board from your specify calculation dialog before you click Ok to dismiss the various dialog and return to your layout. Then put this new field on a Resource Requests layout enter browse mode, click in the field, select Replace Field Contents from the Records menu, select the calculation option and paste this expression into that specify calculation. Then perform the replace field contents operation.

                          Define this relationship:

                          Resources::gIDList = ResourceRequestByClientDteRsrc::INteractionID AND
                          Resources::Resource ID = ResourceRequestByClientDteRsrc::Resource ID AND
                          Resources::cToday < ResourceRequestByClientDteRsrc::ReceiveAgainAfter

                          Now write this script:

                          Set Field [Resources::gInteractionIdList ; contacts::cInteractionIDList ]

                          You'll need to run this script just before you start making resource requests so you might tie it to a button that creates a new interaction record for a given client. The script will work as long as you run it from a layout based on contacts, interactions, or ResourceRequests. You can also use an OnRecordLoad trigger on the Interaction layout to perform this step. (A return separated list of values will match to related records by any one of the values in the list. This list will then "filter" out any resource request records not made on behalf of the current client.)

                          This lays the structure in place so that we can determine whether a given resource was given to a specific client too soon to permit them receiving it again.

                          As a test to see if all works, put a portal to Resources on an interaction layout and add the ReceiveAgainAfter field from ResourceRequestByClientDteRsrc to the portal row.

                          If all is working correctly, you'll see a date in this field each time a client has received a resource and insufficient time as transpired for them to receive it again. (And it will tell you when they can receive it again also...)

                          Once you can confirm that this is working, We can work on a script such that clicking an item in the portal adds it if permitted, to the client's list of requested resources and also what kind of conditional formatting (you may already see how it could work) might be used to change the color of items that may and/or may not be given to the current client.