[Solved] Using the macro: URLButton.py and expanding it....

[dreamquartz]

Forum-post: viewtopic.php?f=13&t=78351#p358909

Hyperlink fields are not built-in. I can offer a flexible macro solution which is supposed to work with common types of URLs and without editing the Python code after the code has been saved in a certain location.

1) Download and open the attached Base document.
2) Open the form named "PythonCode" and save it as plain text in folder %APPDATA%\OpenOffice\4\user\Scripts\python\pyDBA\URLButton.py
%APPDATA%\OpenOffice\4\user\is the path of your user profile where all settings and public macros are stored.

Edit: On you Linux machine the path should be ~/.openoffice/4/user/Scripts/python/pyDBA/URLButton.py

3) Open the 2 tables. "Data" has a list of arbitrary file names in column "Files". In the Setup table you can specify a path for those file names where the "Name" is 'FilePath'. Column "Mail" has arbitrary mail addresses. Column WWW has various https: URLs related to this forum.
4) The query concatenates the file names with the given path from the Setup table and adds the mailto: protocol in order to get valid URLs.
5) The form named "URL Buttons" is based on that query. The form's "after record change" event is bound to the python macro.
6) The buttons are set up as hyperlink buttons (action="Open document/web page") with the corresponding field name as "additional info" property. The button names start with "URLButton" or with "FileButton". The latter variant converts a file path in system notation (C:\...) into a valid URL internally.

If you successfully installed the Python macro, the WWW button should open the active record's https: URL in your browser and the Mail button should open your mail client with a new message to the current record's mail address.
The file button will be disabled for any file that does not exist, otherwise it will open the file:///path/file.xyz with the respective default application for the file type.

Hi Villeroy,

This solution is absolutely wonderful.
It solved a lot of our Clients questions.
Our Client has a 'client'-form with e-mail address, website, facebook, and twitter information.
Our Client wanted to be able to easily open the information from their 'client'-form, and we were able to provide them all. Kudos to you.

However................
Our Client is wondering why can a website not simply be entered as: 'www.example.com' or even as just 'example.com' or as 'example.com/test' in the 'Data'-table, used in your example, instead of https://www.example.com/ or the variations mentioned?
The Web Browser seems to translate most of the add-on security and slashes anyway.

The second question was: Is there a way to just open the File-Folder instead of a specific file within that folder, related to the 'File-function'?
Our Client has a lot of related files for their clients.
These files, however, are not in the same File-Folder, but in separate client-folders. This indicates that that the 'Setup'-table, used in your example, does not work as they would like it to do.

Can the URLButton.py macro be modified to accommodate these requests, and if so, how?

The problem I am facing is that we do not have any Python expertise at this point.

Hope you can give us some advise/support,

Dream

[Villeroy]

Replace the query in my example document with:

Code: Select all

SELECT "Setup"."Value" || "Data"."Files" "Files",
 CASE WHEN LEFT( "Data"."WWW", 5 )='http:' OR LEFT( "Data"."WWW", 6 )='https:'THEN '' ELSE 'http:' END || "Data"."WWW" "WWW",
 CASE WHEN LEFT( "Data"."Mail", 7 ) = 'mailto:' THEN '' ELSE 'mailto:' END || "Data"."Mail" "Mail", "Data"."ID" 
FROM "Data", "Setup" 
WHERE "Setup"."Name" = 'FilePath'

Now you get a http: where no http: or https: protocol is given and you get no superfluous mailto: where it has been stored in the mail field.

One of my applications uses this one:

Code: Select all

SELECT "PATNR", "NAME", "VORNAME", "GEBDATP", "TELEFON", "PATSTRA", "PATORT", 
CONCAT( 'http://maps.google.com/maps?q=', "PATSTRA", ',', "PATORT" ) AS "URL" 
FROM "PATIENT"

and the URL button shows the home address of a person in google maps.

[dreamquartz]

Replace the query in my example document with:

Code: Select all

SELECT "Setup"."Value" || "Data"."Files" "Files",
 CASE WHEN LEFT( "Data"."WWW", 5 )='http:' OR LEFT( "Data"."WWW", 6 )='https:'THEN '' ELSE 'http:' END || "Data"."WWW" "WWW",
 CASE WHEN LEFT( "Data"."Mail", 7 ) = 'mailto:' THEN '' ELSE 'mailto:' END || "Data"."Mail" "Mail", "Data"."ID" 
FROM "Data", "Setup" 
WHERE "Setup"."Name" = 'FilePath'

Now you get a http: where no http: or https: protocol is given and you get no superfluous mailto: where it has been stored in the mail field.

One of my applications uses this one:

Code: Select all

SELECT "PATNR", "NAME", "VORNAME", "GEBDATP", "TELEFON", "PATSTRA", "PATORT", 
CONCAT( 'http://maps.google.com/maps?q=', "PATSTRA", ',', "PATORT" ) AS "URL" 
FROM "PATIENT"

and the URL button shows the home address of a person in google maps.

That looks good.

Will try.

What about the question for the file folders?
Is that also easy to solve?

Dream

[Villeroy]

Yes, sure. Simply use your database engine in creative ways. Add the folder name to the clients table and concatenate the right path. My Setup table with "Name" and "Value" is just a suggestion how to solve the path problem in a platform independent way and you can still use it for the root folder of all client folders.

Code: Select all

SELECT "Setup"."Value" || "Data"."Subfolder" || '\' || "Data"."Files" "Files",
"other fields"
FROM "Data", "Setup"
WHERE "Setup"."Name" = 'FilePath'

Data.Subfolder contains the subfolder names. The backslash applies to Windows only. If your database is used on many platforms:

Code: Select all

SELECT "Setup"."Value" || "Data"."Subfolder" || case when locate('\', "Setup"."Value")>0 then '\' else '/' end || "Data"."Files" "Files",
"other fields"
FROM "Data", "Setup"
WHERE "Setup"."Name" = 'FilePath'

[dreamquartz]

Villeroy,

You made my day.

Thank you so much.

Dream