Hi all,
timesheet scenario, user is prompted to select his name from a listbox, after update event check if a record for this user already exist aka
SELECT pid, ldate FROM tbl_sslog WHERE pid=the id in the listbox AND ldate=CURDATE()
now if there is no record I want to move to record new and update the signin field with the current timestamp, throw up a message and move to a blank for the next user.
1. In this case how do I test this ? (if pid=null?)
If there is a record for this user today with a timestamp in the signout field I also want to move to a new record to account for either 2nd shift or afternoon hours in case he stopped work to see the quack and then returns.
2. And is there a way to do the whole logic without any macro? uno service perhaps...
Thks for your thoughts.
[Solved] Help with on no record logic
[Solved] Help with on no record logic
Last edited by robleyd on Sun Dec 15, 2019 6:25 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with on no record logic
Parent form: SELECT "Name","ID" FROM "Persons" ORDER BY "Name" with a single column table control instead of a list box.
Subform: SELECT * FROM "tbl_sslog" WHERE "ldate"=CURRENT_DATE linked by the common person-ID.
A table control selects records, a subform shows records related to the selected record.
A listbox selects one value by name and writes it into some foreign key of the current record. Listboxes are not designed to select any records.
Subform: SELECT * FROM "tbl_sslog" WHERE "ldate"=CURRENT_DATE linked by the common person-ID.
A table control selects records, a subform shows records related to the selected record.
A listbox selects one value by name and writes it into some foreign key of the current record. Listboxes are not designed to select any records.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Help with on no record logic
Thank you, that solves part of the quandary.
Still as for curiosity - if a select statement does not produce a single row, how would you use this in an if statement i.e. if the record set is empty does the current row have any value or how could one decide to move to a new record
Still as for curiosity - if a select statement does not produce a single row, how would you use this in an if statement i.e. if the record set is empty does the current row have any value or how could one decide to move to a new record
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with on no record logic
If the subform has no records, the new row is availlable anyway unless you turned off record insertion or unless the form has a read-only row set. The parent form needs to have some record selected,though. In the above example you need some person before you can enter any time relating to some person.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Help with on no record logic
Using the API you must first obtain the form object (UNO) for your SubForm, it will have anStill as for curiosity - if a select statement does not produce a single row, how would you use this in an if statement i.e. if the record set is empty does the current row have any value or how could one decide to move to a new record
ImplementationName = com.sun.star.comp.forms.ODatabaseForm.
It is usually simpler if you can use an event to run the macro, for instance if the event is on the SubForm then it would simply be
Code: Select all
oSubForm = oEvent.Source
Code: Select all
oSubForm = ThisComponent.DrawPage.Forms.getByName("MainForm").getByName("SubForm")
Code: Select all
IF oSubForm.RowCount = 0 THEN
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Help with on no record logic
Thank you !!! Doing this little project with a PostgreSQL back end. LO seems to play nicely with V11, does not play with V12. The documentation is excellent, easy to follow.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend