Reference data from omitted record

[esegal]

I have a PROJECTS db that contains layouts for every type of document I use during a project, particularly contracts & change orders. Now, each contractor in a project has one CONTRACT record in the CONTRACT layout, and then will have multiple CHANGE ORDER records afterwards in the CHANGE ORDER layout. So, on my home screen when I select NEW CHANGE ORDER I am taken to the CHANGE ORDER layout and a CONSTRAIN FOUND SET script step isolates all records with rec_type CHANGE ORDER.

Now, when I select a contractor in a new CHANGE ORDER record I would like the contractor's CONTRACT data (from another record that is omitted via the constrain script step) to be auto-entered in some fields (contract date, contract amount, etc.).

So how do I call up specific data from a field in a record that has been omitted? I think another table would do it, but so far I have not been successful as I am still learning relationships and scripting.

Thanks for any help.

[-Queue-]

You need a relationship to the desired contract to pull its data (or merely display it) in the change order. It is irrelevant whether the related record is currently displayed or omitted. As long as there is a valid link to it, you can view or lookup its data.

Before you continue creating the system, I would highly suggest you continue learning about relationships. It will benefit you greatly and make it much easier to develop your solution correctly the first time, instead of having to clean it up later. :wink2:

[Fenton]

I would agree with Queue. The Change Orders (of which there could be several per Contract) really belong in their own table, linked to Contract. You could try and do them the way you're doing, in the same table. But in FileMaker 7 it's easier to just put them in their own table.

View/create them in a portal on a Contract layout. You can export the ones you have now, then import them back into their own table. FileMaker 8 (even the demo version) can create the table for you during the import, with the field names (saves time). It's an option in the drop-down at the top right of the import dialog, for the target.

You would need to link the Contracts and their Change Orders. I'm not sure how you're doing it now. You may need some help with this. It is easier to set up related tables correctly at the start, but it can be done later.

[esegal]

I'll work on developing the solution as you both illustrate. I know I need to get my relationship knowledge together. It's a concept I've been struggling with but am getting better as I spend more time developing them.

Unfortunately FM8 is not an option for me at the moment, but it sure sounds like they've been listening to their users.

Thanks.