Databasejoin where not

[jmoises]

Hello i need help here to figure out how to acomplish my goal

I have a Form to add Students that not were present to make a TEST
What i have in my form 2 Databasejoin elements first is Student Name and second is Test Name
In my first element Student Name have a list of all the Students
In my Second element i have the List of Test
What i need to do is to refresh second element after the user select a Student Name, and not to populate the Test Name that Student has all ready miss, only the test is need to do. (i dont want to report 2 times same studen if he miss a test all ready reported)

I have add this code in second element in the area "Joins where and/or order by statement (SQL)"

WHERE NOT
EXISTS (SELECT test_id
FROM test_not_done
WHERE test_not_done.test_id = test.id
AND test_not_done.student_id = 1)

And i am able to not show test the student all ready miss for the student id 1 but i dont know how can change the 1 for the databasejoin Element Student Name (id) selected.

The Table / form structed is:
test_not_done___id
test_not_done___date_time
test_not_done___student_id
test_not_done___test_id
test_not_done___reason
test_not_done___time

Thank for any direcction i have been working days trying to figure out

 

[cheesegrits]

Try ...

AND test_not_done.student_id = '{test_not_done___student_id}'

... assuming that test_not_done___student_id is the full element name of the student join element. We should replace that placeholder with the current value of the element.

You would also want to set the "where when" to edit only, as the element would have no value in a new form, so that WHERE clause would yield no results at all.

-- hugh

 

[jmoises]

Hugh, thanks for the reply but did not solve my requirment, explaining next:

I cant set the "where when" to edti only, because the form i am using also work on new records.

imagine a Form, with 3 Elements

1 DBJoing "All students Names"
2 DBJoing "All Test Names"
3 Field "Reason"

My idea is that when the user select the Name of the student look in the test_not_done if there are records from same studen not show the "Test Name", only show Test Names, that are not in the test_not_done,

example: I Select in 1 DBjoing "Mark Simon", if this studen never miss a TEST then all the names are render in DBJoin 2 "TEST NAMES" and user can select a test for example "Math", in reason user type "not show", then save the form

example2: I Select in 1 DBjoing "Mark Simon", again, but now in the 2DBJoin All the Test Name will render except the "Math Test Name".

Thanks for any reply

 

[cheesegrits]

Well, the problem is, you can't set it up on new, because when the form is loading, you don't know what student they will select. And join dropdowns only get calculated and populated once, as the form is being rendered on the server prior to loading in the browser.

So that will work when editing, as there will be a student selected, so that WHERE clause will work.

You could try making the test element a cascading dropdown, with the same WHERE clause (and "Where when" set to "both"), and using the student_id as the foreign key. If I'm understanding your data structure correctly, that should work, and populate the test dropdown on the fly, when the student is selected.

-- hugh