Drop down list refresh issue with related table

[Himself]

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.

[imoree]

script trigger with the option

*optional : COmmit Records

REFRESH

End script trigger

[Himself]

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?

[comment]

I don't understand this part:

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.

Could you post a simple file showing this difference?

[Himself]

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?

[Himself]

Ah Ha!! got it.

DropdownRefresh.zip

[comment]

Buildings -< WorkOrders -< Projects

When you are on a layout of Projects, you are selecting a value for the parent WorkOrder::BuildingID field. Not sure that makes much sense, since the choice affects not only the current project, but all projects belonging to the parent work order. Anyway, that is the difference between the two: on the WorkOrders layout, the field is local.

[Vaughan]

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)

They have. Use a popup menu, and specify the value list to display values from a second field (the related value).

FileMaker has been around for like 20 years. You seriously think that it cannot do basic stuff like this?

[Himself]

They have. Use a popup menu, and specify the value list to display values from a second field (the related value).

FileMaker has been around for like 20 years. You seriously think that it cannot do basic stuff like this?

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

[Vaughan]

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?

A drop-down list displays the value actually entered into the field -- say "12", which may be the primary key value for a related city "New York".

How can you expect the drop-down field to display "New York" if it is designed to display what is entered into the field?

Also, drop down lists allow the user to enter anything they like, eg 21 even if there is no related city.

You're using the wrong interface element.

Use an onObjectModify trigger on the pop-up menu to trigger a script that:

commit records

refresh window [ flush cached joins ]

It will work. :D

[comment]

the value chosen in buildings, filters the following value list for the drop down of "Room Locations"

That doesn't seem to fit with the file you have posted. If the purpose of the selection is to narrow down the choices for the following selection, why don't you use a local field?

[Himself]

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?

[Himself]

That doesn't seem to fit with the file you have posted.

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.

[comment]

My initial inclination when posting this was that maybe there was a solution in the relationships that I wasn't seeing

There probably is, but I don't understand your relationships. For example, you say (and your file shows) that "every Project has a single work order associated with it." To me, that's the same thing as saying that the relationship between WorkOrders and Projects is one-to-many - even if some work orders may not have any child projects. Unless you mean that every work order is related to only one project (which would make the relationship one-to-one - and most likely redundant).

And (as I said earlier ) I also don't understand why you need to select a building for a work order while you are on a layout of Projects.

[Himself]

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.

[comment]

But even with with a one-to-one relationship between WorkOrders and Projects, a project knows which work order it belongs to, and the work order in turn already belongs to a building, doesn't it? That's what I don't get with your set up: it looks like you are building this in reverse order, child to parent to grandparent.

[Himself]

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.

[comment]

If I base even one of the forms on Work Order than the "go to layout" is broken

Perhaps, but Go to Related Record[] would work.

[Himself]

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?

[comment]

If you do GTRR from Projects to WorkOrders, you do NOT lose your found set in Projects. Nor do you lose the current record in that set, so Go to Layout [ Projects ] should get you back exactly to where you started.

[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?