Himself

Makes sense to me. I will give that a try as soon as I can and post the outcome. The only minor bump is that the Edit screen is also the New Project entry screen. Now currently a user will click "New Project" from the home screen menu and go right into the edit screen and create the new project and when complete get dumped into the full view tab of the record they just created. To maintain this I would need a goto related record but I would then lose what was previously stored in the found set. I may just dump the user back out to the screen they originated from. Thanks for the help. Do you think there is another solution using table relationships?

I don't monitor the acctual Workers so I noticed that the project I posted is way past due so thier boss should be monitoring that. We are a US public school and our budget has been slashed and people let go so they are probably understaffed and haven't yet adjusted. I tried go to related record... I think but can't remember why it didn't work. Doesn't that too break a previous find? For example, a user needs to edit all the projects relating to a specific building so they go to the find tab and perform the find if the results are greater than 1 they are automatically dumped into the list view tab from there they click on the first project to edit it. Now since the edit screen is based on Work Order and the list screen is based on Projects I use "go to related record" that works and I edit the record and click save. I now need go to related record to get back to projects but now won't the list just show the one record I was editing and not the original list that included all the projects in the building I searched?

Well when a user creates a project from scratch nothing is entered so the entire form is blank the form is based on the Project table but all the WorkOrder elements need to be filled in too. When the record is finished the user is dumped out to the "view only" navigation screens and is now viewing the Project they just entered. The view Only navigation screens both List view and "full or single" view are based on the Project table so the only script step needed is "go to layout" when moving from one to another. This is also true of the "Edit" screen, which is the new record screen except data exists and you are just changing fields. If I base even one of the forms on Work Order than the "go to layout" is broken and the user can get dumped out on any record not the one they were viewing in the previous layout. I can use a find in my navigation but I like the fact that previous search results are still sitting on the form untouched when you return to it. I have attached some screens of the forms the "Edit" screen is in Edit mode so you can see what fields are local. I highlighted the Building and Location fields and moved the Edit field off the drop down field to see them. The other screens are just view only screens but you see how it a user clicks on what appear to be tabs but are in fact buttons. This is also just the project side the work order screens are nearly identical minus the "Job Queue" which is a whole other story.

Well this Database was requested by someone higher up the foodchain than me. I told them that Projects are redundant as they are just workorders with expected start and stop times while work orders are open ended driven by demand. These projects are also navigated separately from Workorders the two don't share any space together in front end operations they have separate search screens data entry screens etc.. not my wishes either in my opinion it added extra uneccessary work. Well perhaps it was my folly but I chose not to reinvent work orders to satisfy Projects and decided to just add another table (Projects) with projected start and stop times and related it to work orders, afterall the work order table had 99% of the fields for projects. Now by using Projects as the forms source table only project records are visible and searched, the same is not true in the reverse navigating the WorkOrders screens shows projects but through scripts and tricks they don't ever pop up. There are over 20,646 work order records and only 344 project records currently in my database, the relationship is 1-1. Does this help? Thanks for the time btw.

I did not include a dependant value list in the simple file and perhaps I should have however it doesn't really change the problem. The problem is refreshing the form after selecting a value from a value list that is dependant on a related table not the one tied to the form. This problem occurs with drop down lists and pop up menus alike. Something that is not evident in the sample and I don't think has been clear is that the relationship between the work order table and project table is not 1 to many. There are many more Work orders than there are Projects not all work orders have projects associated with them but every Project has a single work order associated with it. My initial inclination when posting this was that maybe there was a solution in the relationships that I wasn't seeing since this problem seems to be caused by the relationship between Projects and WorkOrders. Maybe by using some magical table joins could get this whole thing working? While I think I get most of what the Relationships area is about it is probably my weakest area and probably horribly constructed and under utilized.

Yes the drop down list displays the value stored, that is the crux of the problem. This is 2012 the relational database is dominant no longer do flat files reign. I can't even really think of an instance when I would use a drop down list that isn't storing a record "ID" value and if there are reasons they are few and far between. Record "ID's" shouldn't be in the forefront of the user interface so doesn't this effectively make the drop down list useless? Well no it doesn't because there are "work arounds" so you can get some of the features of the drop down list like auto complete and scrolling. What is odd is that Filemaker allows the drop down list to show just the second field when selecting a value but not after the value is selected. Can't they just easily add the option to show the second field only after selection?

Actually I fully believe they can do simple stuff. I like Filemaker, sure it has its limitations and quirkiness but everything does. Sorry if you took offense to my statement, however it still holds true since drop down lists and pop up menus are different and one can't always be swapped for the other. Your statement only strengthens my point since they "have been around for 20 years" and this feature exists in pop up menus. So why has it yet to appear in drop down lists they have had the time and they can implement the function? Now while using a pop up menu will work in showing the right building chosen immediately it still doesn't work for my specific case since the value chosen in buildings, filters the following value list for the drop down of "Room Locations" and the only way to get that refresh is to click on a blank area of the form. I think I have exhausted the simple solutions. I can't use pop up menus. I can't change the table that drives the form to work orders since it breaks other parts of the DB. I might be able to use script triggers to commit the record once a value has been selected but I have to check all the users copies of FM. Its funny you remember all the years people complained about Filemaker not having event triggers (a la Access style and yes I know someone will say they had them all along with calculation fields or something). Well now it has them and I am not sure I can use them because of what version of FM everyone could be using. I suppose I could just make a whole new form that matches the one I am having difficulty with, base it on a new table and do away with relational DB structure and just copy the actual values from the original to this “duplicate form and table” and let the user select the values directly and then copy the ID's back to the original copy using a "Save" button. Seems like a lot of work to prevent a user from having to click on the form to get a refresh? oh well...

Ah Ha!! got it. DropdownRefresh.zip

I apparently don't know how!?! How awful is that? I made a simple file but can't figure out how to attach it. What’s the secret?

Commit records is not optional a refresh alone doesn't update the Edit box; but than the user would be hit with record validation messages since they may not have filled in every other part of the form. I also have to find out what version of FM everyone is using and get them all upgraded. I suppose I could turn off the record validation and use a scripted save button for record validation. Any other ideas?

I have a form which uses a drop down list to store an ID and an edit box overlay to show the related value chosen. (still baffled that FM hasn't just made that a native option) The problem I am noticing is that the Edit box only refreshes when one clicks on a blank area on the form. I have drop down lists in other forms that refresh their edit boxes as soon as a value is selected. The difference being that the ones that refresh immediately, the drop down list field is from the table the form is based on, but if the drop down list field is based on a related table it doesn't refresh immediately. How do I get it to refresh without changing the forms base table reference or clicking on a blank area of the form? I have just let this behavior go but now the users are getting annoyed by it. I am on FM 11 but I am not sure all the users are. Thanks for any help or even just let me know if this is something that just is the way it is.

It works! Thanks again for the push in the right direction.

I wouldn't expect many records per user per day. Ideally they attempt to schedule a Technician to be in one building for an entire day but life is not always that simple and the need to move Technicians around always presents itself. I would say the max might be 4 locations in a day but the majority will be 1 or 2 locations in a day. I had come up with using a calculation field in the Appointment table to identify the destination location. That would then link to the Mileage table nicely. The calculation in that field was my next step so I will look into your suggestion tomorrow since my day is now over. Thanks for you time and consideration.

Based on the previous record. So for example: 1, 3/27/2011, 8:00 AM, 9:00 AM, Rob C, AHS, Install Stuff 2, 3/27/2011, 9:00 AM, 12:00 PM, Rob C, CAO, Meeting 3, 3/27/2011, 8:00 AM, 10:00 AM, Glen B, AMS, Alpha Project 4, 3/27/2011, 1:00 PM, 4:00 PM, Rob C, AMS, Alpha Project 5, 3/27/2011, 1:00 PM, 4:00 PM, Glen B, AMS, Alpha Project 6, 3/27/2011, 10:00 AM, 12:00 PM, Glen B, Out, Doctors Appointment As you can see the records won't necessarily be created in the proper order but using the date and time stamps they can be sorted by Date,Person, StartTime. This is why the problem is a problem if the start location and destination were sitting on the same record it would be easy.

In my organization we have 15 buildings within a county that we have fixed distances between and offer mileage reimbersment. Our Technicians use a FM DB to track inventory and work orders on the inventory. Within this DB is a built in Scheduling system to assign the Technicians to a building for all or part of the day in instantances where the Technician travels between buildings they are entitled to mileage reimbersment. The Director of Technology now wants the FM DB to track the mileage for the Technicians since the information is already in the DB and produce a report for the Technician to sign off on. Here is what I have to use: Tbl_Appointments Appointment_ID,Date,StartTime,EndTime,UserID,LocationID,Comment Tbl_Mileage LocationID_1,Distance,LocationID_2 How can I make a report where the calculation requires a lookup of a distance on a table using two records from another table? The theoretical report would produce something like this: |User Name |Date 3/28/2011 Total Miles 6 | 1-AHS 0 Miles | 2-AMS 4 Miles | 3-CAO 2 Miles | |Date 3/29/2011 Total Miles 2 | 1-CAO 0 Miles | 2-BMG 2 Miles | |Total Mileage for Date Range 8 Any thoughts? Many thanks in advance for anyone who commits time to this problem.