Page 1 of 1
Value on parent form depending on subform
Posted: Thu Feb 10, 2022 10:04 pm
by 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.
Re: Value on parent form depending on subform
Posted: Fri Feb 11, 2022 3:31 am
by kev1n
Is the status to be updated whenever there is a change in the subform or just after saving?
Re: Value on parent form depending on subform
Posted: Fri Feb 11, 2022 10:11 am
by Duski
Only after saving.
Re: Value on parent form depending on subform
Posted: Sun Feb 13, 2022 11:58 am
by 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
Re: Value on parent form depending on subform
Posted: Sun Feb 13, 2022 3:32 pm
by 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.
Re: Value on parent form depending on subform
Posted: Sun Feb 13, 2022 8:26 pm
by 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]));
Re: Value on parent form depending on subform
Posted: Tue Feb 15, 2022 1:15 pm
by 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
Re: Value on parent form depending on subform
Posted: Tue Feb 15, 2022 1:18 pm
by kev1n
Replace the last line with:
Code: Select all
nuRunQuery("UPDATE tbl_knihy SET dostupnost = ? WHERE catalogue_id = ?", array($row[0], "#RECORD_ID#"));
Re: Value on parent form depending on subform
Posted: Tue Feb 15, 2022 3:59 pm
by Duski
Hi Kev, thank you very much !
It works !!!