Ok, so a relationship does sort of solve this issue in that the value list will populate without a commit record. Yeah, but still no cigars since the fields I need to display are calc fields and valuelists cannot display calc fields. That was why the idea of using SQL to extract the calc fields, then put them into a indexed field was so enticing. Again the SQL works, the values appear in the debugger, but they don't appear in the value list without the commit.
The conundrem continues. Any ideas or suggestions? I'm feeling very thick on this hot July day in Cape Cod.
Presumably, your reason for trapping the commit is to prevent users from overwriting data by mistake, or perhaps preventing them from creating "dead" records. Also, it seems you're trying to create hierarchical value lists (value in field A drives the available values for field B).
Given those assumptions are correct, I suggest you change your workflow a bit. Create a "sessions" table where each user gets a record for a given login. Create your new record there, using global fields. When the user wants to make a change, grab the data and move them into the globals. When the user is happy with the changes, save them back to the original record. This will allow you to commit to the sessions record and avoid the issues with value list population, while simultaneously supporting rollback (if the user isn't happy with the changes, you haven't touched the original record).
My goal is simply to be able to revert a record, but gettign the valuelist to populate from an SQLSelect necesitates a committ record.
The layout involves a parent table and a child portal, so I was hoping to having to use globals. But you are probably correct that it is the best solution.
I won't make it to DevCon this year, but it seems like transactional processing in FileMaker merits a lot more attention than it gets.
Another option might be to use a Sessions table, like I suggested, but put the value list field in that table instead of the current table / record. I don't know if that would work; I haven't tried it.
You might also try opening a separate window to a layout that has just the value list fields you want, using globals and a relationship from them. Then save the values back to the original record when the user closes the window.
Just some things to try.
Have you considered using the “Virtual List” technique here?
Seems it might keep the building of the selector list away from the data and allow you to base a value list on the global data, all without committing the current record.