Help writing a macro which recalls a record based on a field

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Help writing a macro which recalls a record based on a field

Post by DrBones »

Hello,

I'm a chiropractor and I created a openoffice base db and writer form to complete my daily patient notes. My current setup is to complete a note and click a button which activates a macro to save the note to pdf in a particular location.

Until recently, I haven't been able to save the record into the database due to some conflicts which I think I've ironed out. My form has 220 fields and is pretty busy.

Now that I can save records, I want to create a macro which would populate the writer form with the last record for the selected patient.

I had someone helping me with this, but his schedule hasn't allowed him much time to help.

Attached are 1. A clean copy of the DB with 1 SOAP record and some Dummy patients. 2. A copy of the writer form set to DB and 3. A sample PDF of what the form looks like when "pdf'd".

While this solution isn't the most elegant, it actually works quite well for me.

I appreciate any help/advice anyone could provide.

Dr. Bones
Attachments
Bunny,_Bugs_06-22-10_SOAP.pdf
Exported Soap Note Sample
(82.74 KiB) Downloaded 363 times
SOAPforForum.ott
Template Form for entering daily patient notes
(48.95 KiB) Downloaded 237 times
DocumentationforForum.odb
Backend for entering daily patient notes
(101.8 KiB) Downloaded 243 times
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Help writing a macro which recalls a record based on a f

Post by RPG »

Hello

Your complete question is not so clear to me. When you want have it in the way you describe it now I think it is a little difficult. But I think we must start with working with this idea you have now.

When you want follow the way I think it is a good way then
a) You have to download the BaseTools of Benitez
b) The document you want have as a result must be inside your database.

For me it is also the first time I work with the BaseTools. I did have seen them for a long time but never realize they are strong. For you they are maybe a little difficult to understand but with a little explanation they can work easy. On this moment I don't know how stable they are but I do trust Mr Benitez as a good programmer and when there are bugs he will repair the bugs.

For you must knew some names in two documents and adjust a little the macro. I have add a lot of comments to the macro who calls the macro of Benitez. I think it can be easy for you to use it.When you understand this then maybe you can use other parts of the BaseTools.

When it is not working for you then it is maybe the first step

Romke



Code: Select all

sub toNewForm(oEvent as object)
' This is an example
' First select a ID in your form in which you start.
' Open then the new form and select that record.
' It use the BaseTools of Benitez
'Create  Variables for source Form
dim oButton
dim oForm
dim oIdentifier,sIdentifier

'Create for New form Document
dim oNewDoc,sNewDoc
dim oNewForm,sFilter,sNewFormName,sFieldname

' Create variable for this sub
dim  oCon ,sValue


' Give values to the variables for the source form
sIdentifier="ID" 'Give the Name for the variable which you want use

' Give value for the Destination form
sNewDoc="Dlookup2" ' Give here the name of the form
sNewFormName="Formulier" ' This is the form name as you can see in the form navigator

' The next is important when you run it in the wrong way
' It can only with a button in your form.
' Don't run this programm from IDE but  activate it with a button
oButton=oEvent.source.model
oForm=oButton.parent
oCon=oForm.ActiveConnection ' This ecpect that your new form is in the same database.

' When you get here an error then posible there is no library of that name
' Instal the Basetools of Benitez. It is an extension of OOo.
'http://extensions.services.openoffice.org/en/project/BaseTools
if not globalscope.BasicLibraries.islibraryloaded("BaseTools") then 
	' Load now the library
	globalscope.BasicLibraries.loadlibrary("BaseTools")
 end if


' Compose the the filter in this case only a field name with the value'
' This line you have to adjust.It depends of sevaral things.
sValue= str(Utils.getResultSetColumnValue(oform,oForm.findcolumn(sIdentifier)))
sFilter= chr(34) & sIdentifier & chr(34) & " = " & svalue

' Now all variable must be set.
' test if the library you need is loaded


'Now pass all the variables to the sub
' you call a function what comes back with an object point to the form 
' in your new form document
openFormDocument(sNewDoc,oCon,sFilter,sNewFormName)'.reload
' Maybe delete the comment sign  when you use the butto more then once
end sub
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Help writing a macro which recalls a record based on a f

Post by Arineckaig »

I endorse fully RPG's recommendation of the Base Tools extension and would add that that I have learnt much from the excellent book "Database Programming" also by Roberto Benitez.

In reply to your specific request for a macro to populate a Writer form with the last record for the selected patient I offer one possible route in the attached file. You will see the file is a Writer document (not a template) but it should be write protected. The document's form, however, can be linked to a separate Base .ODB file. When suitably linked it can be opened to act in much the same way as a Form (Writer) Document embedded in that Base file: it can read and write data to the database file without appearing even to open the latter. You will appreciate, however, that the Form in the attached Writer file is currently linked to the location of its original datasource ("DocumentationforForum.odb") on my hard drive, and it will not know where that file is located on your machine. You will need therefore the following procedure to restore the correct link.

1. Remove the "Read-only" write protection attribute [currently not applied]
2. Open the file
3. Check View>Toolbars>Form Design
4. Turn on that toolbar's edit mode (button #2)
5. Turn on the form navigator (button #5)
6. Open the Form's properties dialog at the "Data" tab.
7. Click the [...] near the "Data Source" box, and browse to connect the form to the correct .ODB file. If the database has been registered you can instead select its registered name directly from the Combobox.
8. If there were to be any subforms, you would need to connect them in the same way.
9. If all goes well, Save and Close the Writer document.
10. Apply a "Read-only" write protection attribute to prevent layout changes.

The Writer file can now be opened independently of its datasource Base file, but even if "Read-only" it will still permit records to be read, updated, deleted or added. This independent form, however, does not replace or supplant any forms that are already embedded in the Base file, which can still be used. You may, however, find it helpful to keep the Writer file separate so that it gives you direct access with no need to open the Base file, but I would caution against using both embedded and independent forms open at the same time to change records in the same table.

You will see I have made two additions to your original Form Document template file: a small ComboBox at the top left corner and an skeleton macro called "PatientLatest". To simplify calling up an existing patient's latest record the focus is set to the Combobox on opening this Writer file. Space does not permit a drop down list for the Combobox but otherwise it works as usual - start typing any name, or use the up/down keys to move through the list. Whenever the ENTER key is pressed it triggers the macro. After a short delay the macro filters the Form to show just a single record - the latest record for patient whose name was in the Combobox. Select any other name in the Combobox, press ENTER and the form is filtered for that patient's latest record. To regain access to all records clear the Combobox, press ENTER and hopefully the Navigation Toolbar will show that all records are again available.

I should mention the contents of the Combobox are filled by reading a list of existing patient names from the SOAP table. Furthermore it is essential that the Combobox is NOT bound to any field in the SOAP table: otherwise, there is a serious risk of inadvertently over writing data.

Tine has not permitted me properly to test the macro for bugs especially as my test datasource had so few records. Thus, it is merely a skeleton to give you one possible route that I am sure could be improved upon. For security you should backup your data before playing with my Writer file as the risk of unforeseen bugs corrupting the data must exist.

In summary, the macro waits until a ENTER key is pressed in the Combobox. It then reads the contents (sTest) of the Combobox. If those contents are an empty string it clears all filters, reloads the Form and aborts. Otherwise it runs this SQL query:
SELECT "SoapP1ID", "Date", "PatientName" FROM "SOAP" WHERE "PatientName" = sText ORDER BY "Date" DESC LIMIT 1.
You will see how this query selects just one record that is the latest date for the named patient, but it supplies the unique "SoapP1ID" primary key for that particular record. In turn, this key is used as the filter for the form. To catch typos and other potential errors. however, the macro before applying the filter runs a check that the query found a suitable record: otherwise, it aborts with a warning message that the name could not be found.

Please come back if you have problems or where I have failed to explain the macro with adequate clarity.

EDIT: I have found a bug in the file I initially attached to this post - it has been deleted. Please use the replacement in my later post.
Last edited by Arineckaig on Sun Jun 27, 2010 8:55 pm, edited 2 times in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Re: Help writing a macro which recalls a record based on a f

Post by DrBones »

Thanks for your replies!

I've added Basetools and pulled the macro out of the sample SOAPforForum.odt and I'll be testing it tonight. I looks great and makes a lot of sense. I appreciate all your help and will reply back tonight after checking everything.

Dr.Bones
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Help writing a macro which recalls a record based on a f

Post by Arineckaig »

I regret the file that I uploaded in my previous post has an unfortunate error in it. The Patient Name combobox key pressed event also triggers the macro. Please use the attached revised file. Many apologies for inconvenience caused.

** EDIT ** 1 July 2010: See my later post for an improved macro to avoid problems arising from use of a filtered form.
Attachments
SOAPforForum2.odt
File replaced by later version - 1July 2010 post
(78.16 KiB) Downloaded 257 times
Last edited by Arineckaig on Thu Jul 01, 2010 12:29 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Re: Help writing a macro which recalls a record based on a f

Post by DrBones »

Hi Arineckaig,

I'll take a look at the updated code. When I was working with the prior code I realized there was a problem, you beat me to the punch! Let me take a look and get back to you. Unfortunately one of my databases became corrupt and I've had to rebuild from a previous day's backup and have been working on that.

DrBones
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Help writing a macro which recalls a record based on a f

Post by RPG »

Hello

It is good to read you have made a daily backup. But it can be good to separate your database from your forms and queries. See this thread.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Re: Help writing a macro which recalls a record based on a f

Post by DrBones »

Hi Romke,

Thanks for the suggestion, I currently do hourly backups of sensitive data two places locally (but two different buildings) and remotely daily to the cloud. I was looking at Migrating everything over to H2, but I'll look at the multi-file approach, I wonder if it would support multiple users? More stuff to investigate.

Still looking at the macro from Arineckaig, it is working funny, but I think it may be me, can't figure out why, time to do more snooping.

Eoin
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Re: Help writing a macro which recalls a record based on a f

Post by DrBones »

Hi Arineckaig,

So I've spent an hour using the new macro and here are my findings.

When I execute the existing print to PDF macro and then manually save the record, I then click the add a new record "star" on the navigation bar. This will add a new record, but when I use your macro by putting in the name of the patient I just created a note for, the macro won't see the new record. If there is a previous record, it will populate the form with that record, but the record number does not auto increment. If, say, I was on record 3/3 when I clicked the add a new record "star" when I execute your macro, the record count changes to 1/1. Funny behavior.

DrBones
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Help writing a macro which recalls a record based on a f

Post by Arineckaig »

but when I use your macro by putting in the name of the patient I just created a note for, the macro won't see the new record.
Where a Combobox is not bound to a field in the data source table, its contents list will not be updated until the Form Document itself has been reloaded. Closing and re-opening the Form Document will achieve this but at some inconvenience.

My suggested macro is only directed to opening the most recent record for an existing patient. For this purpose it uses a filter which has the effect that only one record from the data source is then accessible. I suggest that when you are entering new records, or accessing recently entered records, you should use the DataForm unfiltered: new records entered during the current session should be readily accessible even if the DataForm is unfiltered. In other words there should generally be a need for a filtered DataForm only to access records entered in earlier sessions. The filter route with all its drawbacks is a far from ideal but relatively simple to program: it would, of course, be preferable to keep the DataForm unfiltered, but to search for the particular RecordSet that matched your criteria. Please forgive my laziness for not programming both that route and the immediate updating of the Combobox. **See a suggestion for the latter in my later post.**

Given the limitations of the filter route you might consider the merits of having two largely similar Form Documents: one for entering new records and the other for access to a patient's most recent record where the latter does not allow additions but is saved and stored separately from the Database file. For an accounting database where there tends to be a huge number of records, I tend to use similar but slightly different Form Documents when entering new transactions or accessing old ones.
when I execute your macro, the record count changes to 1/1. Funny behavior.
The record count changes to reflect the filtering of the DataForm so that only one, but hopefully the relevant, RecordSet is shown.
Last edited by Arineckaig on Tue Jun 29, 2010 4:01 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Help writing a macro which recalls a record based on a f

Post by RPG »

Hello

You can use HSQLDB with multiusers but it is better to start a new thread.

For the problem with the new form made by Arineckaig I think an easy solution is maybe possible.
Use a navigation toolbar. On the navigation toolbar is a button for the filter. Maybe this is enough for your problem. When you want use a navigation toolbar then you have to choose between
a) a control toolbar
b) The toolbar who belongs to the document.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Help writing a macro which recalls a record based on a f

Post by Arineckaig »

I would strongly support RPG's advice that use of the Form Navigation Toolbar is generally a better route than resorting to Macros. In particular, the toolbar's "Form-Based Filters" button permits a powerful method of applying complex selection criteria: once its obscure documentation in the Help file has been mastered.

Further to my earlier post I should have mentioned that a Combobox, even if not bound to a field, does have a .Refresh() method which should re-run the SQL command that supplied its contents. Thus, adding the line "oEV.Source.Model.Refresh()" as the 12 line before "Exit Sub" would ensure its list is updated every time the Combobox is selected with nothing in it.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Help writing a macro which recalls a record based on a f

Post by Arineckaig »

FWIW I had some time yesterday to play with my earlier macro as I was never really happy with the filtering route. The attached file has an updated macro that now avoids use of a filter, but no doubt has other problems when put to work on a serious database. In summary it still uses an SQL command to find the "SoapP1ID" field of the selected patient's latest record, but uses it so that the form simply navigates to the matching record.
Attachments
SOAPforForumRev.odt
Updated version
(79.96 KiB) Downloaded 281 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
DrBones
Posts: 22
Joined: Fri Oct 17, 2008 1:37 am
Location: Long Island, New York

Re: Help writing a macro which recalls a record based on a f

Post by DrBones »

Thanks for all the suggestions, I'll take a look at the updated macro tonight or tomorrow as time allows.

I appreciate all the help!

Dr.Bones
OOo 3.0.X on Ms Windows XP + Ubuntu; DSL;
Post Reply