[Solved] Help with on no record logic

Creating and using forms

[Solved] Help with on no record logic

Postby gkick » Thu Dec 12, 2019 3:24 am

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.
Last edited by robleyd on Sun Dec 15, 2019 6:25 am, edited 2 times in total.
Reason: Add green tick
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 238
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Help with on no record logic

Postby Villeroy » Thu Dec 12, 2019 1:30 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28351
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with on no record logic

Postby gkick » Fri Dec 13, 2019 3:13 am

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
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 238
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Help with on no record logic

Postby Villeroy » Fri Dec 13, 2019 12:38 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28351
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with on no record logic

Postby UnklDonald418 » Sat Dec 14, 2019 9:29 pm

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

Using the API you must first obtain the form object (UNO) for your SubForm, it will have an
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   Expand viewCollapse view
oSubForm = oEvent.Source

worst case something like
Code: Select all   Expand viewCollapse view
oSubForm = ThisComponent.DrawPage.Forms.getByName("MainForm").getByName("SubForm")
That form object will have a RowCount property, so
Code: Select all   Expand viewCollapse view
IF oSubForm.RowCount = 0  THEN

would be a way to test for an empty ResultSet.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1307
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Help with on no record logic

Postby gkick » Sun Dec 15, 2019 5:09 am

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.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 238
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest