Value on parent form depending on subform Topic is solved

[Duski]

I have a form (book catalogue) with subform object (grid) containing loans of books. I'd like to retrieve status of a book on a parent record (available or loaned) based on its loans. If there is no loan or for all loans exists a date of book return, then the status in parent form should be "available". If there is at least one loan with date of book return = Null, then the status in parent form should be "loaned".
Can U help me pls. ? I'm not experienced in SQL
Thanx in advance.

[kev1n]

Is the status to be updated whenever there is a change in the subform or just after saving?

[Duski]

Only after saving.

[kev1n]

Somethting like this (untested)

Code: Select all

SELECT IF(COUNT(*) > 0, 'loaned', 'available')
  FROM catalogue
 LEFT JOIN loan ON loan.catalogue_id = catalogue_id
WHERE catalogue_id = '#RECORD_ID#'
AND loan.book_return IS NULL

[Duski]

Thank you Kev, it works, but I had to change LEFT JOIN to RIGHT JOIN

Now I have the apropriate value in display object "disp_stav" on form "frm_knihy".
How can I write this value on save into the underlying table "tbl_knihy", column "dostupnost" ?
Thank you in advance.

[kev1n]

Use the PHP AS event to update it:

Code: Select all

$q = "
SELECT IF(COUNT(*) > 0, 'loaned', 'available')
  FROM catalogue
 RIGHT JOIN loan ON loan.catalogue_id = catalogue_id
WHERE catalogue_id = '#RECORD_ID#'
AND loan.book_return IS NULL
";

$t = nuRunQuery($q);
$row = db_fetch_row($t);

nuRunQuery('UPDATE tbl_knihy SET dostupnost = ? WHERE catalogue_id = '#RECORD_ID#'', array($row[0]));

[Duski]

Maybe a syntax error ?

Snímka obrazovky 2022-02-15 131226.png

After save nubuilder says:

Snímka obrazovky 2022-02-15 131358.png

[kev1n]

Replace the last line with:

Code: Select all

nuRunQuery("UPDATE tbl_knihy SET dostupnost = ? WHERE catalogue_id = ?", array($row[0], "#RECORD_ID#"));

[Duski]

Hi Kev, thank you very much !
It works !!!