[Solved] Help with on no record logic

[gkick]

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.

[Villeroy]

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.

[gkick]

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

[Villeroy]

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.

[UnklDonald418]

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

oSubForm = oEvent.Source

worst case something like

Code: Select all

oSubForm = ThisComponent.DrawPage.Forms.getByName("MainForm").getByName("SubForm")

That form object will have a RowCount property, so

Code: Select all

IF oSubForm.RowCount = 0  THEN

would be a way to test for an empty ResultSet.

[gkick]

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.