Better to make each entry into a different record of a related table. Think in terms of a portal where one field in the row is a date field and the other field in the row is a location field. A third field would be a match field that matches by location ID. You can then sort either the portal or its relationship by the date field in descending order to sort the most recent location to the top of the list.
(This could be done with a large text field, but that's not nearly as effective a way to manage this data as can be done with a related table.)
Thanks, sounds good. I've gone with the attached relatinoship and script. However the portal in artwork to location makes all entrys identical when you update it. I had a new location pk which matches the fk in location.
You have your keys backwards. You need the records in location to match to the ArtworkID field not artwork records matching to location by location ID.
Thanks Phi, all set!
Hi Phil, actually... as the above script takes you to the location layout and then comes back, I am having issues with the screen jumping to the top of the original layout (ie. when it comes back). My location field in artwork is in a tab along the bottom edge. When you perform the script everything works fine with the portal but the screen appears to jump up as it goes and comes back to the artwork layout and then auto goes to the first tab in the tab order.
Is there a way to configure this. I've tried scroll window down, and also a go to field step at the end of the script to take you to a field positioned next to the location one but the screen always jump up to the first tab.
The first tab of a tab control or the first field in the layout's tab order?
Is this a script performed via a script trigger? Which one? (You may need to end that script with an Exit Script [False] step after a go to field puts the cursor where you want it.
It's also possible to create new records in a related table without changing layouts. It takes a bit of creativity with your relationships but the script isn't much more complex than you currently have.
Say your relationship is currently this:
ParentTable::__pkParentID = ChildTable::_fkParentID
Add another occurrence of ChildTable with this relationship:
ParentTable::GlobalField = ChildTable|Create::__pkChildID (enable allow creation of records for ChildTable|Create.)
Then this script will create a new ChildTable and link it the current ParentTable record by __pkParentID;
Set Field [ParentTable::GlobalField ; "" ]
Set Field [ChildTable|Create::_fkParentID ; ParentTable::__pkParentID ]
After the second set field step, GlobalFIeld will get the value of __pkChildID via the "create" enabled relationship. You have to clear the global match field in the parent table before another set field to the related table occurrence will create a new record.
it goes tot the first field in the layout's tab order.
Trigger wise I have tried on save and on exit. I have also tried introducting a separate button to o the script but all result in the same jump.
Thanks, I'll look into those other points.
all sorted, had to make it go the an adjacent field and then window scroll down too!