There a lot of info missing from your post.
I’ve tried using a portal on a separate layout but any record I add gets overwritten when I select another client.
A portal to what table? located on what layout? with what relationship linking the portal's table to the portal's? And how do you "select another client" on this layout?
It seems to me that you probably have two kinds of Journeys: Journeys actually taken, client A went to destination 1 one date and time 7, and "regular" journeys--those trips that happen on such and such a time every Friday or every 1st of the month...
You may need two tables for the two since they need slightly different data to be recorded. Then, the list of regular journeys can be used to generate a list of the actual journeys for a specific bus and driver.
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 ]
Any help or pointers would be greatly appreciated.
Some of my questions are still unanswered. This is a portal to what table? How is it related to ShoppingBus?
What you describe, in general, is a new many to many relationship. You have one Journey--the bus trip, but instead of linking it to one client, you now need to link it to many clients--who can take many different bus trips. Thus, you need a relationship such as:
And "Journey" can either be the same table that you are already using for your car "journeys", or you might create a separate table for this. It will depend on whether you need to see Car and Bus journeys in the same report or not.
Hopefully this will answer your questions Phil. The portal was related to the client table which was in turn related to Shoppingbus via the clientID. I realise now I think this wrong. I need to look at your suggestion of a many to many relationship. It doesn't necessarily have to relate to the Journey table as the job will have been created and the Car journeys will not need to be on the same report.
Hopefully this helps.
It doesn't really change what I suggested in my last post. As I pointed out in it, the "Journey" table in my example need not be the "journey" table you use for car trips.
So are you saying I could create a new journey table to use for the bus passengers? If this is the case what would you suggest I link the new table to, the client table? I've included a screen shot just to show the relationship tables as they are at the moment.
I believe that I've already posted the answer to that:
If you want to use a different table for Journey, that might be:
This need not be linked to any of the table occurrences shown in that last screen shot. For Client, you can duplicate the existing Tutorial: What are Table Occurrences? of Client and use that duplicate in the above relationships.
Setting up "Table Occurrence Groups" is a common method, also referred to as Anchor Buoy for breaking up a large complex "web" of relationships into simpler, smaller groups of relationships, each dedicated to a different layout or group of similar layouts. This can make working with the design of your database easier.
Having now revisited the whole Bus Trip part of the database after your post I decided to start it again from scratch.
I have taken your suggestion on board and have created two new tables. One called BusTrips and the other Passengers. The passengers table is joined to client via the _fkClientID. (I’m not sure whether I need to join the BusTrips to the Destination Table, as most of the destinations are already in this destinations table). The Passenger table is joined via a field called Frequency in the BusTrips Table.
My question 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’m also assuming that the button / trigger should go on this table / layout and not the main journey table / layout or the Passenger / portal layout.
Again, I’m assuming the script needs to copy client ID or client name and then Go To the layout Passenger and Set the Field with client ID or client name in the portal table. However I’m not exactly sure of the mechanics of this and need a little advice.
Passenger table is joined via a field called Frequency in the BusTrips Table.
Sorry, but that field name makes no sense to me. The two match fields in Passenger should match to ID fields in the other two tables: Bus Trips and Clients.
The rest of your post is also confusing. A common way to work with a many to many relationship such as this is to put a portal to the join table (passengers) on a layout based on one of the other two tables (clients or bustrips). A portal to passengers on the clients layout would list the bustrips linked to that client. A portal to passengers on the bustrips layout would list the passengers scheduled to go on that bus trip.