Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Jonah74

  • Rank

Profile Information

  • Gender
  • Location
    Litllehampton UK

FileMaker Experience

  • Skill Level
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
  • OS Version
    High Sierra

Recent Profile Visitors

1,899 profile views
  1. Hi Lee In this case it was just a rhetorical question to see if it could be done without actually putting it into the application scenario. However I see your point and I've given the scenario to the members replying now. Regards Chris
  2. Hi Steve OK, Field A is a client cost that would have been quoted when a job for the client was booked for a journey, i.e £27.00. However, the true cost of the journey actually became £32.00 because of the additional mileage incurred so this is entered into Field B. I need to record the additional cost of £5.00 (the difference) in Field C (sorry not field 3). Then, the original cost field A, needs to be updated to reflect the true cost as shown in Field B £32.00. Hope this now makes sense!! Hi, Thanks for the help. The script triggers seem to be the way to go. Thank you.
  3. Am I righting in thinking I can use a case statement to evaluate two fields and return an answer into a third field ie: Field A has the number 27 in it. Field B has the number 32 in it. Could the case statement look at the difference and put the difference in Field 3.The difference being 5? I’m also trying to get this to do the math on data entry, so the figure in Field A is 27 but when the user enters the new figure in Field B (i.e 32), it looks at that difference between A and B, copies the difference of 5 to Field 3 and then also copies the figure 32 from Field B into Field A thereby updating Field A to the new value. Help, suggestions or thoughts will be greatly appreciated.
  4. Jonah74

    Portal Help

    Sorry the 'car side' refers to the original use of this database i.e. Social car use and volunteer drivers for the charity as first explained. (see screenshot). That's why I wondered if I could still use the same table or whether I would need to create another TO. Are you suggesting creating a TO for the original table journey and then adding the join tables as per your example to  that? As for pop ups, this may be impractical as there are something like 1800 clients which is why I suggested a type of pick from a search or find field, but I'm open to suggestions for this.
  5. Jonah74

    Portal Help

    Yes this makes sense. I thought I would have to create another table layout, I just wasn't sure where this would fit. I'm going to add this to the journey table as per your tip and hopefully that will take care of table layout. One thought though. As the driver table is already being used on the car side is it OK to use this for the bus trips or would you suggest a TO.? My other question is once I have created the join table as per your suggestion how do I then 'pick' the clients to go on it? Do I use the same method as on the car journey and if so, how would I get a 'listing'? I thought that is where I would have to use a portal. BTW, just wanted to say thanks for your help so far, really appreciated.
  6. Jonah74

    Portal Help

    I think I may not have given you enough information regarding the relationships and you may need to see the whole relationship table which might show why the clients, destinations and drivers are joined. (see screenshot). At the moment the way it works is one client will call the office and want to book a driver for a journey. A new record is created which becomes the ‘journey’ and the serial number becomes the journey reference ID. The client field is populated from the client table. Likewise, the client’s destination. Finally, when the user selects the journey date and time this triggers a script. This is the mechanism behind it all allowing the user to select drivers that are available for that particular day and time. (see screenshot 1).  So going back to your question: Will there be a single Journey record each time a Client is taken to their destination, or does a Journey refer to a single outing for the driver, in which he/she may pick up multiple clients? I think you have to say the way it is set up at the moment each journey refers to a single outing for the driver, in which he/she may pick up multiple clients. One of the main reasons behind this is because drivers have to be booked anywhere up to four weeks in advance so the charity knows it has drivers and can carry out the service for the given weeks, hence why minibus, shopping bus trips and outings are booked the same way as single client car journeys even though they have multiple clients. This is the bit I’m trying to develop. I want to use the original journey reference that was created for the trip but maybe switch to another layout to add multiple clients and multiple destinations. (see screenshot 2). If this is the case would I still use, as you have suggested, two tables, fares and journeys and how would that now tie in with the already existing journey? I‘m also guessing that I would need to create a new layout to accommodate the fares and journeys data and is this what the portal would be based on, one of these tables?
  7. Jonah74

    Portal Help

    We currently have a FM12 Database that’s used as an appointments system for a local community transport charity and was designed to allocate and book volunteer drivers time and vehicle from a pool of volunteer drivers in order to take residents of the community to medical and private appointments. We also run Minibus Hire, Club Bus And a Shopping Bus Service, which are also entered onto the system in order to record the bookings. In order to enhance the current Shopping Bus Service the system has to be able to allow multiple clients, (passengers) to be booked on a specific Bus Route and Service. These minibuses run on set routes everyday collecting the clients and taking them to either their single destinations, like supermarkets, or multiple destinations similar to a hail and ride service visiting various places. A printout then has to be generated displaying the passengers taken, their fares cost, their destinations and return trips so that drivers have the correct information when carrying out these journeys. Office staff must also have the correct information in order to maintain the service and determine if the correct revenue is received at both passenger level and service level. What I’d like to be able do, is add clients from the existing client table along with their destinations from the existing destinations table and add them to another layout / table using a portal if possible. (Please see the attached diagram / screenshots that probably illustrate it better than I can describe it.) Once each route has its quota of passengers (which is usually the same clients most days and most weeks) the passenger list needs to be printed to give to the bus driver so he knows who he is collecting and where they are bound. I’ve got an idea of how I’d like it to look / work (see screenshots) and with a bit of research on portals and TO’s I’ve made some assumptions. As both the client and destination tables are all ready linked to the main journey table I’m guessing I have to create two TO’s one for client and one for destinations and add these to a join table. However there are some things I’m not sure of and need some help with. For instance, should the portal be based on the client table or something else? How do I populate the portal from a search field using a different layout? Can I accommodate the clients and their addresses, plus the destination and addresses, on the Portal? Is it possible? Sorry about the length of this post but there was no other way of explaining what I’m trying to achieve but any help or advice will be very much appreciated. Diagram.pdf Screenshots.pdf
  8. Jonah74

    Creating a list

    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.
  9. Jonah74

    Creating a list

    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.
  10. Jonah74

    Creating a list

    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?
  11. Hi Thanks for the info but I've gone the Set Field route. I created two set fields which worked but as the pop up button gets its data from a value list, namely: Live or Cancelled, using the Set Field on it's own would insert blanks ("") irrespective of whether Live or Cancelled was selected. By inserting the If statement in front and then adding a script trigger (On Object Save) to the popup button it does the job perfectly. Problem solved and thank you for your advice. Test: Cancel Script If [ Journey::Journey Status = "Cancelled" ] Set Field [ Journey::Journey Cost; "" ] Set Field [ Journey::Office Fee; "" ] End If
  12. The Journey Cost and Office fee are currently free text fields set to currency format because as at the moment each journey can have different costs as does the Office fee. If I change these fields how does the auto enter calculation affect this because as I understand it users cannot enter anything into these fields.?
  13. Thanks for the reply but could you elaborate please?
  14. Hi  Can anyone tell me how to replace a particular fields contents with different values for individual records or serial numbers rather than a found set? At the moment I can get it to replace field contents but it does it for the whole found set not just the one individual record I want. For instance, I have a DB that records car journeys which are given individual record numbers (automatic serial numbers) and has a cost in two fields. What I'm trying to do is: if someone cancels a journey I'd like to be able to click a button that says Cancel and a script runs that looks at that individual record ID and inserts £0.00 in two fields and overwrites the cost thats in it. I'm guessing it's going to be a script with an If statement in it but I just can't figure it out. Any help would be greatly appreciated.
  15. Hi I had this exact same message when someone clicked in a field to select something from that field and before going to a table to select their choice. It appears the cursor doesn't exit that particular field properly but I found a sort of workaround to it. I drew a box using the rectangle tool and added text to it saying "click here to confirm". The user then clicks that box which does nothing except make the user move the cursor out of the original field that throws up the error message and then allows them to select the button that goes to the table and layout they need. So far I have found this has worked but I think it may be a bug with FM and the TABS Tool. When I get more time I will investigate this. In the meantime I hope this trick might help.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.