[CLOSED] [Linking forms foundsets] Lost in relations...

Classic Servoy
1

Hi, I’m developing in internal Servoy solution to track our employees training courses’ expenses.

There are 3 tables in the database (that are worth mentioning here):

  • Employees = employee_id, etc.

  • Courses = course_id, etc.

  • Expenses = expense_id, FK employee_id, FK course_id, etc.

Current solution design:

The “main” form shows the “main_courses_expenses” form.

the “main_courses_expenses” form shows:

  • “frm_courses” = to create or edit a “training course” (one ‘course_id’)
  • “lst_courses” = the list of all “training courses” (all ‘course_id’) (you can select a ‘course_id’ from the list and it shows in the above editing form)

the “main_courses_expenses” form also lets you access:

  • “frm_course_expenses” = to create or edit an “employee expenses reimbursement form” (one ‘expense_id’) for the ‘course_id’ selected in the previous form
  • “lst_course_expenses” = the list of all “employee expenses reimbursement forms” (all ‘expense_ids’) for the selected ‘course_id’
    (you can select one “expenses form” from the list and it shows in the above editing form)

From the “main” form navigation bar you can also show the “main_employees_expenses” form.

the “main_employees_expenses” form shows:

  • “frm_employee_expenses” = to edit an “employee expenses reimbursement form” (one ‘expense_id’) for the ‘employee_id’ of the “employee expenses reimbursement form” selected in the “main_courses_expenses” form
  • “lst_employee_expenses” = the list of all “employee expenses reimbursement forms” (all ‘expense_id’) for the selected ‘employee_id’
    (you can select one “expenses form” from the list and it shows in the above editing form)

When a user selects an “employee expenses reimbursement form” (one ‘expense_id’), I can read the associated ‘employee_id’

QUESTION:
What is the best way to pass the ‘employee_id’ value of the “frm_course_expenses” form to the “main_employees_expenses” form?
That is, how do I limit this form’s foundset of expense_ids to that particular ‘employee_id’?

BECAUSE:
I tried all sorts of tab relations or to load a foundest through an SQL query.
All to no avail.
I’m lost.
Bad design? Bad designer? Both?

Thanks.

SEE ATTACHED IMAGE FOR A VISUAL REPRESENTATION

servoy_flowchart.gif
servoy_flowchart.gif1551×782 32.4 KB

2

Bob Olog:
…how do I limit this form’s foundset of expense_ids to that particular ‘employee_id’?..

Place the employee_id in a global field and use it as the left side of a global relationship, and then base the form tabpanel on that global relationship.

3

Thanks Westy, thinking again about the “relations” way lead me to the solution.

Quite convoluted I may say…

First I had to base the “main_employees_expenses” form on “employee_id” (employees table) to be able to relate the contained forms to the “employee_to_expenses” relation.

That being done I reverted the base of “main_employees_expenses” form to “expense_id” (expenses table).

Which in return allowed me to link the “main_employees_expenses” tab on the “main” form to the “courses_to_expenses” relation.

SUMMARY: “main” table courses (=> course_id) → “main_employees_expenses” courses_to_expenses relation (=> expense_id which has employee_id as a foreign key) → “form_employee_expenses” employees_to_expenses (=> expense_id foundset for this particular employee).

I don’t know why Servoy prevents me from linking tabs to whatever relation I want regardless of the db table the underlying form is based on.