[Solved] Programming a 'tab' function with hsql
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Programming a 'tab' function with hsql
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.
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.
Re: Programming a 'tab' function with hsql
Hello,
what about this, you have a table "DATA", columns ID integer, TABNUMBER integer and TEXT varchar, then this query: which concatenates the number of TABS from column TABNUMBER with TEXT.
R
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"
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
I am exploring your suggestion and so far so good.
Thx,
Dream
Thx,
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
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
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.
Re: Programming a 'tab' function with hsql
Sorry, cannot follow you.
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
Uhm.....F3K Total wrote:Sorry, cannot follow you.
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.
Re: Programming a 'tab' function with hsql
I hit F4, select the query top left and drag the column headers down to the writer document.
R
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
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
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
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
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.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
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
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.
Re: Programming a 'tab' function with hsql
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:sample file attached, have fun LOL
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
- 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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
Thanks for the response.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:sample file attached, have fun LOLCode: 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
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.
Re: Programming a 'tab' function with hsql
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
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
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Programming a 'tab' function with hsql
@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.
Dream
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"
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.