I am having trouble with a seemingly simple idea. I have a table called "Jobs" and its primary key, "Job_ID". I want to create a global field within the table that displays the "Job_ID" of the current/active record.
This sounds simple because if you are in the current record, the current "Job_ID" can be displayed. Why I need it is because I have another table, "All Vendors", that has a cartesian product, "x", relationship with the "Jobs" table. I want to pull this number into the "All Vendors" table using a global calculation field. The relationship between the tables relates all records to all records, and I want the "Job_ID" of the current job to show as a global field in the other table without adjusting the table relationship. I also want it to change when the current job changes. The only way I know to do it is using a script to update the number each time I need it to be, but I would rather not for simplification of the database.
To further clarify, I am using the field within a layout using the "Jobs" table. In essence, what I am trying to do is, set up a layout that shows all records (vendors) on the left and "selected" vendors added to the job on the right with a script I created. The selected vendors lie within a join table and I am trying to filter out the selected vendors from the "All Vendors" table by using the join table and current "Job_ID" global field either within the "Jobs" table or the "All Vendors" table.
Previously I have included in my script that adds selected vendors to the join table, a step that sets a "Hide" field to "1" and then I filtered the portal that using the "Hide" field. But this becomes tedious because every time your done you have to clear/reset all the "Hide" fields for each record back to "0" using a loop. I am trying to simplify by using tables to filter them out based on the join table with the selected vendors in it. Most currently, I am doing that but having to have a script step that updates the global field with the current "Job_ID".
Let me know if I need to clarify further. If the problem in the first paragraph above can be solved, it will solve my problem. But if I am going about it all wrong, I would happily take a better suggestion. Thanks