Database Join Element Data Where Clause

[Sourcenet]

Hi

I'm trying to generate a dropdown using the database join element, but only showing data relating to another field in the table.

Example:

I have a list with one of the fields is Job Number.

I have a databasejoin element that gets the job number from the database but it shows all the job numbers instead of only the job numbers relating to the current row clicked.

I know I will probably need to use the Data-Where option but i don't know how to use it. Ive tried:

where (select jobs.job_number from jobs where jobs.id = {expense___jobid_raw})

but that returns no data.

I hope this makes sense.

 

[cheesegrits]

So I'm assuming 'jobs' is the joined table (the one you are joining to)?

What is the "foreign key" (FK) on the jobs table that references your form's table?

If that field is called 'fk', the where clause would be ...

{thistable}.fk = '{rowid}'

-- hugh

 

[Sourcenet]

The foreign Key in the jobs table is id. but i'm still getting blank data when i change your code to this: {thistable}.id = '{rowid}'

I will try to explain with some screenshots perhaps that would make it easier.

So I have a list that shows all the jobs form the jobs table, in this list I use the Related Data option that shows expenses logged against this job - this is done because of the databasejoin.

When clicking the view option this opens a list, with all the expenses to the job. this is correct.

but, when clicking on the add option it takes me to the expense form, to enter a new expense, this is where i would like to auto populate the dropdown with the correct job number based on the row that was clicked to add the expense.

The job_number Element in the Expense table is currently set to be a databsejoin from the jobs table with value job_number and label job_number, but this populates all the Job numbers in the job table and not the one based on the selected row.

I'm trying to explain this best possible, i hope this helps?

Thanks for your support.

 

[troester]

If I understand it correctly you want to set the default.
If you are coming from the related data list view this list is filtered with &your-dbjoin(FK)-element=rowid-of the parent list
You can get the filter value in your default (eval) via

$filterval = (int)FabrikHelperElement::filterValue(X);
return $filterval;

X= the element id of your dbjoin

 

[Sourcenet]

Hi Troester

I have tried it but still getting all the jobs in the dropdown and not the one that was clicked.

Ive tried:
$filterval = (int)FabrikHelperElement::filterValue(jobs___job_number);
return $filterval;

Not sure if i'm using the correct element id?

 

[troester]

No, the element id is a numeric value
View attachment 17224

This won't reduce your dropdowns (this has to be done with the Data - where...) but will set the value in your new record (even if user doesn't have write access for this element).
Maybe {thistable}.id = {jobs___job_number} will do in the Data-where (I didn't try).