Stored procedure with data from different tables

Question

We want to use a stored procedure with values from different tables. Is there any possibility?

Answer 1

There are three options for using values from different tables in your stored procedure.

A) Find the values in the Stored Procedure

If the values you want are related to the current record then you can follow the relationship to its source.

B) Add a Table View to get the fields you need

You can add a Table View to your current table and have it return the field values you need. You can directly add those field values as inputs to your Stored Procedure.

C) Add a hidden lookup to the current table and prompt the user

You can create a Hidden Lookup that appears as a prompt field. dbFront will either save that field before running the button or you can have the selected value passed to the stored procedure. If you pass the value to the stored procedure then it becomes responsible for saving the value.

1. Add a new field to the current table that would be a foreign key to the lookup you want,
2. Create the lookup relationship so that dbFront can create the lookup UI,
3. Show the field on the current form and set the lookup options (fields, sort-order),
4. Hide that field on the current form,
5. Add that field as a prompt field,
6. (Optional) Include that field as an input to the Stored Procedure,
7. (Optional) Have the table trigger block any attempts to set that field.