[Solved] Programming a 'tab' function with hsql

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] Programming a 'tab' function with hsql

Post by dreamquartz »

Hi All,

I would like to be able to program a specified number of 'tabs' or the distance of a tab, based on a result-set.

I have a table that with a set numeric value in HSQLBD, and based on that value I would like to have a 'tab' set.
I need to get to a specified position in a Writer document to place a character.
It is like filling a table with a character in a specified cell.

Example:
Table Value: 3 => place character on tab distance 3 cm
Table Value: 7 => place character on tab distance 7 cm

Any suggestions?

Thanks in advance,

Dream

03/09/2020; Re-opened because of partial success.
Last edited by dreamquartz on Sun Jul 24, 2022 2:25 am, edited 3 times in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Programming a 'tab' function with hsql

Post by F3K Total »

Hello,
what about this, you have a table "DATA", columns ID integer, TABNUMBER integer and TEXT varchar, then this query:

Code: Select all

SELECT "ID", REPEAT( CHAR( 9 ), "TABNUMBER" ) || "TEXT" "TABTEXT" FROM "DATA"
which concatenates the number of TABS from column TABNUMBER with TEXT.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

I am exploring your suggestion and so far so good.

Thx,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

It turns out to be working great in the DataBase I work with, but it does not translate to the required functionality in Writer.

I was hoping that by simply programming this function under SQL, the result set could be used in an odt file.

When running in Writer, it only shows numbers instead of moving to the tab-position and show text.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Programming a 'tab' function with hsql

Post by F3K Total »

Sorry, cannot follow you.
DreamTab.PNG
DreamTab.PNG (10.79 KiB) Viewed 5510 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

F3K Total wrote:Sorry, cannot follow you.
DreamTab.PNG
Uhm.....

I am apparently missing something.

I normally create a Query and do Crtl+F2 for the fields I need in Writer.
It seems that you are doing something different.
I cannot figure it out.

Can you please elaborate?

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Programming a 'tab' function with hsql

Post by F3K Total »

I hit F4, select the query top left and drag the column headers down to the writer document.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

I use that F4 once a while, but I am working with a lot of forms, where multiple people need to be entered. I need to use 'Next record' a lot, and I have not seem that in a toolbar. That's why I use 'Ctrl+F2' more.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

It works partially though.

Did not realize I have to modify my question to include the following: "Can I pre-program the TAB length? " It is pre set, but I need to be able to get to any place in a paragraph, including, but not limited to 0.01 cm.
The length to the TAB is dependent of the result set as well; just to make it more fun..... LOL

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

Question:
Is there a way to set the length of the tab on a LO level?
Is there a way to connect to the field in LO that holds the data for the value of the tab length, so that it can be used for programming in HSQL?

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Programming a 'tab' function with hsql

Post by F3K Total »

You can add/modify/delete tabstops by code.
To move the first tabstop in first paragraph from position 3 cm to 6 cm and back you can use e.g the following:

Code: Select all

Sub set_Tab
    oTextCursor = thisComponent.Text.createTextCursor
    aTabstops = oTextCursor.Paratabstops
    aTabstop = aTabstops(0)
    if aTabstop.position = 3000 then
        aTabstop.position = 6000
        aTabstops(0) = aTabstop
        oTextCursor.Paratabstops = aTabstops
    else
        aTabstop.position = 3000
        aTabstops(0) = aTabstop
        oTextCursor.Paratabstops = aTabstops
    endif
End Sub
sample file attached, have fun LOL
Attachments
DreamTabStop.odt
(11.5 KiB) Downloaded 214 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

F3K Total wrote:You can add/modify/delete tabstops by code.
To move the first tabstop in first paragraph from position 3 cm to 6 cm and back you can use e.g the following:

Code: Select all

Sub set_Tab
    oTextCursor = thisComponent.Text.createTextCursor
    aTabstops = oTextCursor.Paratabstops
    aTabstop = aTabstops(0)
    if aTabstop.position = 3000 then
        aTabstop.position = 6000
        aTabstops(0) = aTabstop
        oTextCursor.Paratabstops = aTabstops
    else
        aTabstop.position = 3000
        aTabstops(0) = aTabstop
        oTextCursor.Paratabstops = aTabstops
    endif
End Sub
sample file attached, have fun LOL
Thanks for the response.
It apears not to be working in my situation when looking at the download.
I tried all variations I could think of, from using the embedded version of HSQLDB to my version, the split version 2.5.
i would like to be able to feed the macro with fixed data from a table as variables.

I have a long list of fixed combinations of numeric values that are listed in a random order every time I access the list.
This means that e.g. the '1-6-4-3' combination is always the same, but will end in a random order listed when used.
All the numbers in the '1-6-4-3' combination represent a different tab length on the same line in a Writer document.
The combination will always have the following sequential tab order: 3.15-2.1-1.8-5.2.

My problem is that the line where it will be on the Writer document varies, because of the random order created by the DataBase.
This tab order can be of line 1, and running the sequence again, it might end up on line 7.
Because there are more combinations possible than there are used on 1 page of the Writer document (a set max allowed; with a minimum of 1) it can happen that this combination will not even show on the Writer document.
There are over 70 combinations in the DataBase, and max 12 lines available on the Writer document.

I am looking for a method to fix that.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Programming a 'tab' function with hsql

Post by F3K Total »

Sorry,
I showed you a way how to modify the tabs in writer by code.
It's also possible by code to read database values, to feed the macro with fixed data from a table as variables.
A combination of both will work, but you need to write the code.
And to learn how to do that. It's independent from the used database-version.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Programming a 'tab' function with hsql

Post by dreamquartz »

@F3K Total
Long overdue, but finally the results I was looking for.
It turned out to be that the location of the tabstops could easily identified, based on a background.
The background is a fixed format, and I used it as a basis.
There are 2x12 columns, and 12 rows.
All the columns represent a task, all the rows represent people.
Using the random sequencing https://forum.openoffice.org/en/forum/v ... lit=random principle, each person will end up with maximum 4 tasks in total.
There will be 2 tasks in the 1st 12, and 2 tasks in the 2nd 12.
Now sorting the the order from low->high, per group of tasks, and ordering them as such was relatively simple.
First taken the 1st group, order it from, low->high and UNION ALL the 2nd group sorted from low->high.
Now that the order was known, the positions of the 1st 12 tasks was based on the task number; so task 7 leads to 7 tabs.
However, if the resultset showed 3, 7 for the 1st group, the 1 tab is set to 3, while the 2nd one is set to '7-3'.
To get the 2nd group, the principle is the same, however, because it is 2x12=24 in this case, and the resultset for the 2nd group is for example 2, 12 (equal to position 14, 24), 3rd tab is set to '14-7' and the 4th tab is set to '24-14'.
The only thing to do now was to align the tab locations, to the background.

Code: Select all

SELECT
	 "RandomTasks".*,
	 rownum( ) "Rank"
 FROM
	 (
		 SELECT
			 "RandomID",
			 "Task1Low",
			 "Task2Low",
			 "Task1High",
			 "Task2High",
			 REPEAT ( CHAR (9), "Task1Low" ) || "Task1Low" ||
				 REPEAT ( CHAR (9), ABS ( "Task2Low" - "Task1Low" ) ) || "Task2Low" ||
				 REPEAT ( CHAR (9), ABS ( ( "Task1High"+ 12 ) - "Task2Low" ) ) || "Task1High" ||
				 REPEAT ( CHAR (9), ABS ( "Task2High" - "Task1High" ) ) || "Task2High" "Scenarios",
			 RAND( ) "Rand"
		 FROM
			 "Random"
		 ORDER BY
			 "Rand"
	 ) AS "RandomTasks"
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply