Trip Log with images in form & report
Posted: Wed Jun 12, 2019 12:20 am
whilst browsing the LibreOffice forum i came across this post https://ask.libreoffice.org/en/question ... parameter/
the author may have reached a dead end or possibly/hopefully persevered & accomplished his mission.
the user uses his vehicle to tour the country.
he spends a variable number of nights at various sites for which he pays a fee.
he requires a database to store the trip info (start & end (date/time/location/mileage)).
he needs to calculate the cost, time, distance.
it occurred to me that a successful outcome would require the use of several different but commonly used methods.
1) a filter table.
2) externally stored images.
3) a query which joins the current table row with selected fields from the previous table row (to enable calculations).
4) a form which displays an image.
5) a form button which opens a report for the currently selected trip.
6) a report which displays an image. Note: the report builder extension is required for openoffice download version 1.2.1 from here
the author of the topic uses libreoffice but i have used openoffice for this example.
i don't contribute to the libreoffice forum but do use libreoffice as a front end for postgresql because there is no suitable driver available for openoffice 4.1.6.
the bugs that the libreoffice developers have introduced make it an unpleasant experience.
if you open this example in libreoffice then every form window will open with the same x,y dimensions & the report will output blank pages.
ok so this is a fairly simple example but for a newbie it's quite a challenge.
i guess that most newcomers to base will have a specific project in mind but without the knowledge gained by experience many will struggle & eventually submit.
i don't want to spend time explaining everything so if anyone needs help with any particular aspect/function then please use the search function or post your question in the open office base forum.
this has been more of a restructuring exercise than anything else with the addition of 2 input forms & 1 report.
we have 3 queries which provide the code for the list boxes.
we have 1 other query 'qF_TripViewer' which selects & organises all trip data.
a view 'vF_TripViewer' was created from the query 'qF_TripViewer' & is the data source for both the form 'f3.Trip Viewer' & the report 'rReport'.
the form 'f1.Trip, Location, Site & Image' permits the input of base data.
there is no table relationship(foreign key) between trips & locations. this is because we may visit a location numerous times on different trips.
i use a windows 10 computer & the picture file path is in windows format. it may be necessary to amend the file path in accordance with your operating system.
note: report builder will not tolerate spaces in the file path (replace all spaces with '%20') forms are not so fussy.
the form 'f2.Trip Log' is used to input trip data.
when first loaded one of the list boxes will likely show the bound field rather than the visible field. this occurs in both libre & open office. just hit the 'Refresh Grid' button.
the form 'f3.Trip Viewer' is read only.
in appearance it is almost identical to the original.
a trip is selected via a list box & the selected value is saved to a filter table ("tFilter"."TripID2").
a button 'Open Report' is linked to a tiny macro which opens the report.
our report is to display info for the currently selected trip only so we need to filter our data source 'vF_TripViewer'.
the sql command shown below is saved within the report.
it is this sql command which enables us to use a single data source for both form & report.
in a nutshell the view 'vF_TripViewer' is filtered by the table "tFilter"."TripID2".
i live in the uk & have input a fictitious trip 'Newcastle Round Tour' (just for fun). it's a trip down the east coast & back up the west coast of england & wales.
it's always wise to store images externally in order to reduce bulk & data corruption a split db is also a must.
this example is an embedded hsqldb 1.8.0.10 file.
the images are stored externally.
because image files are bulky the db is too big to upload to this forum but can be downloaded from >> http://www.mediafire.com/file/0s3o380bk ... s.zip/file
the author may have reached a dead end or possibly/hopefully persevered & accomplished his mission.
the user uses his vehicle to tour the country.
he spends a variable number of nights at various sites for which he pays a fee.
he requires a database to store the trip info (start & end (date/time/location/mileage)).
he needs to calculate the cost, time, distance.
it occurred to me that a successful outcome would require the use of several different but commonly used methods.
1) a filter table.
2) externally stored images.
3) a query which joins the current table row with selected fields from the previous table row (to enable calculations).
4) a form which displays an image.
5) a form button which opens a report for the currently selected trip.
6) a report which displays an image. Note: the report builder extension is required for openoffice download version 1.2.1 from here
the author of the topic uses libreoffice but i have used openoffice for this example.
i don't contribute to the libreoffice forum but do use libreoffice as a front end for postgresql because there is no suitable driver available for openoffice 4.1.6.
the bugs that the libreoffice developers have introduced make it an unpleasant experience.
if you open this example in libreoffice then every form window will open with the same x,y dimensions & the report will output blank pages.
ok so this is a fairly simple example but for a newbie it's quite a challenge.
i guess that most newcomers to base will have a specific project in mind but without the knowledge gained by experience many will struggle & eventually submit.
i don't want to spend time explaining everything so if anyone needs help with any particular aspect/function then please use the search function or post your question in the open office base forum.
this has been more of a restructuring exercise than anything else with the addition of 2 input forms & 1 report.
we have 3 queries which provide the code for the list boxes.
we have 1 other query 'qF_TripViewer' which selects & organises all trip data.
a view 'vF_TripViewer' was created from the query 'qF_TripViewer' & is the data source for both the form 'f3.Trip Viewer' & the report 'rReport'.
the form 'f1.Trip, Location, Site & Image' permits the input of base data.
there is no table relationship(foreign key) between trips & locations. this is because we may visit a location numerous times on different trips.
i use a windows 10 computer & the picture file path is in windows format. it may be necessary to amend the file path in accordance with your operating system.
note: report builder will not tolerate spaces in the file path (replace all spaces with '%20') forms are not so fussy.
the form 'f2.Trip Log' is used to input trip data.
when first loaded one of the list boxes will likely show the bound field rather than the visible field. this occurs in both libre & open office. just hit the 'Refresh Grid' button.
the form 'f3.Trip Viewer' is read only.
in appearance it is almost identical to the original.
a trip is selected via a list box & the selected value is saved to a filter table ("tFilter"."TripID2").
a button 'Open Report' is linked to a tiny macro which opens the report.
our report is to display info for the currently selected trip only so we need to filter our data source 'vF_TripViewer'.
the sql command shown below is saved within the report.
it is this sql command which enables us to use a single data source for both form & report.
in a nutshell the view 'vF_TripViewer' is filtered by the table "tFilter"."TripID2".
Code: Select all
select * from "vF_TripViewer" where "TripID"=(select "TripID2" from "tFilter" where ID=0)it's always wise to store images externally in order to reduce bulk & data corruption a split db is also a must.
this example is an embedded hsqldb 1.8.0.10 file.
the images are stored externally.
because image files are bulky the db is too big to upload to this forum but can be downloaded from >> http://www.mediafire.com/file/0s3o380bk ... s.zip/file