Creating a list

[Jonah74]

I have a database that contains a client table, a journey table and a destination table. From these tables I need to create a list of clients that go on regular shopping trips and print them out for a bus driver. I’ve tried using a portal on a separate layout but any record I add gets overwritten when I select another client. Can anyone give me any clues how I can select specific clients and add them to a list?

[LaRetta]

Hi Jonah,

 

I suggest that you create a summarised list report from your join table - Journeys.  Hopefully they are joined using unique, meaningless IDs similar to the attached ERD.

 

When the report is created from this perspective, you can pull data from both the Customers table and the Trips table.  

[comment]

I have a database that contains a client table, a journey table and a destination table.

 

That's not a sufficient description, I am afraid.  It seems obvious that a client can have many journeys - but how many destinations can one journey have?

[Jonah74]

I think I need to give you guys more detail on what I’m trying to achieve as I don’t think I gave you enough in my first post.The database was designed for a Community Transport Charity which matches elderly or infirm  clients with volunteer car drivers to take them to hospital, doctors dentist and other such appointments. At the moment the sole function of the database does just that. On the main layout / table a journey is created by drawing information from the three other tables, a client, a free driver and a destination. A serial number is then created which acts as the journey reference number and is then printed for the driver for that particular time / day or week.

 

However, I now want to add additional functionality. On top of the volunteer car service the charity organises daily minibuses to three local supermarkets. Only some of the same clients from the client base go on one of the shopping trips on one day of the week either weekly or fortnightly and usually the same day. It is this group I need to capture and present as a list for the volunteer bus driver, not as a job because the job will already have been created to cover this trip. 

 

On top of this the charity also organises weekly club bus trips where again, some of the clients go on outings for the day to various places of interest, restaurants or tea rooms etc. Again, I would like to create a list from the clients wishing to go so that the driver has a listing of clients on his bus that he can cross reference on the day instead of the way they do it at the moment using scraps of paper. 

 

As I said in my earlier post I created a new layout based on the journey table and added a portal using clients which adds a client using a button on the main journey layout but…. when I added a client, using the following script, all it does is overwrite the client instead of adding to it. I am guessing there is something wrong with either the relationship or the table it’s being based on. 

 

Show Custom Dialog [ Title: "New Clients"; Message:

"Add the client to either the Shopping Bus or the Club Bus using the buttons below."; Default Button: “Shopping Bus”, Commit: “Yes”; Button 2: “Club Bus”, Commit: “Yes”; Button 3: “Cancel”, Commit: “Yes” ]

If [ Get (LastMessageChoice) = 1 ]

Go to Layout [ “Client Details” (Clients) ]

Set Variable [ $ClientID; Value:Clients::_pkClientID ]

New Window [ Name: "Shopping Bus"; Style: Dialog; Close: “Yes”; Minimize: “No”; Maximize: “Yes”; Zoom Control Area: “No”; Resize: “Yes” ]

Adjust Window  [ Resize to Fit ]

Go to Layout [ “ShoppingBus” (ShoppingBus) ]

Go to Portal Row [ Last ]

Set Field  [ Journey::_fkClientID; $ClientID ]

End If

Any help or pointers would be greatly appreciated.

[comment]

I am having difficulty understanding your script - mainly because it goes to a layout of a "ShoppingBus" table. This table hasn't been mentioned before and the context is not clear.

 

 

Perhaps I am missing something, but it seems to me this may actually be more complex than you make it sound. Based on this description:

 

the charity organises daily minibuses to three local supermarkets. Only some of the same clients from the client base go on one of the shopping trips on one day of the week either weekly or fortnightly and usually the same day. It is this group I need to capture and present as a list for the volunteer bus driver

 

you would need a table of DailyTrips, and a join table of TripSubscribers to sit between it and Clients. In the TripSubscribers there would be a field to indicate the frequency of the subscription, e.g. "Wednesday". Then, to generate the list of passengers for a given date, a script would start by finding the subscribers for the given date's day-of-week.

 

Once that is done, you can begin to handle exceptions - i.e. one-time cancellations and additions. Only then you would be able to print the list of passengers - and we are assuming here that this list is printed and forgotten, otherwise you would need another table or two to keep it.

 

Bi-weekly subscriptions add even more complexity.

 

 

Perhaps you have a different workflow in place that makes this process simpler - if so, please explain it.

[Jonah74]

Having now looked at the script it was indeed wrong. Not just because of the problems you pointed out but because of where it was placed. It was on the Main Journey Table and did not have a Go To Related Record line it anyway so would never have found the correct client. So I have started again.

 

I have taken your suggestion on board and have created the two new tables. One called BusTrips and the other Passengers. The passengers table is joined to client via the _fkClientID and the BusTrips is joined via a _fkDestinationID, (as most of the destinations are already in this the destinations table). The Passenger table is joined via the field called Frequency. 

 

My question is now and it’s the one thing I’ve been trying to solve myself is the script and where the button / trigger should go in order to run the script. I’m assuming I should be putting the portal on the Passenger table as this will be the listing and this portal should be based on the client table. Because the clients are in a separate table I am also assuming that the button / trigger should go on this table.

Again I’m assuming the script needs to copy client ID or client name switch to the layout Passenger and Set the Field with client ID or client name. However when I try this I am still getting an empty fields in the portal so I still seem to be missing something I just don’t know what.

 

BTW there is no workflow in place for the scheduled trips other than a manual spreadsheet hence the need to create something more robust and that compliments the social car database I have put in place.

[comment]

I am not sure where you're standing at the moment and what the script in question is supposed to accomplish. Typically, periodical subscriptions to a trip are only a "blueprint" for the actual list of passengers on a specific instance of a trip. You need to decide how this discrepancy will be handled (in terms of workflow) before you can build a mechanism to facilitate the chosen method.