Ask Your Question
0

Synchronise scrolling of subforms

asked 2020-09-08 23:08:42 +0100

gkick gravatar image

updated 2020-09-11 21:11:46 +0100

Hi,

The image below shows a couple of sub forms joined together, the sub on the left being for data entry while the sub on the right displays the daily running balance with the forms underlying sql being read only. Perhaps there is a better design possible...

The form works well if there are less records then a screen, full not displaying scrollbars. However once lots of records both vertical toolbars appear needing to be scrolled independently.

It there any way to synchronize those scrollbars, so if you scroll one the other follows?

Thanks for any ideas.

image description

C:\fakepath\30dshort.PNG

C:\fakepath\90dshort.PNG

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-09-09 06:01:01 +0100

Ratslinger gravatar image

Hello,

Have thought of this a number of times in the past. It is not easy as it would require macros and as you can see it isn't very good to look at.

Again your situation is made more difficult because of you using HSQLDB embedded. This lacks functions you could use and has multiple problems when dealing with Queries especially certain ones calling others.

With that said, in a recent post we dealt with running balances. As noted, the totals do not change on each line if the date remains the same. So in order to present my approach here, I will also give a method to remedy that problem. This can be fixed by using Sequences. For a bit of background see my answer in this post -> DateDiff between records on same table and the link/sample there.

This solution will not require any macros. It will require three View's and a Query. A Sequence is an on-demand auto-increment type generator. You create with SQL (execute from menu Tools->SQL...):

CREATE SEQUENCE S

And can reset with (posted for convenience - no need for doing this):

ALTER SEQUENCE S RESTART WITH 0

You get the next value with:

next value for S

The value doesn't matter - only that it is unique. By applying this to the correct sequence of transactions, the total will be created for each row and not grouped by date. So there are three Views. First one CFtotals puts the items in sequence and creates the total of debit/credit for each row. The second SeqCFtotals adds a sequence number onto the result of the first View. The third SeqTotals is basically the same as the query from the running totals answer except to use the sequence number.

The query RunningTotalSequence gets the table information and add the running total to it. This needed to be done in this manner so the query (will be used in the form) does not need to have Run SQL command directly turned on. This would cause the form not to work.

Now the form is based on the query and the column for the running total has the column property Enabled set to no. This is to avoid mistakenly trying to modify the column. It would not allow but you would get an error message. If you modify or add/delete items, the new totals can be gotten with a Refresh - done from Form Navigation toolbar. Will properly sequence and total items.

Sample ------ SeqAndOneTableForm.odb

Hope this is an acceptable method.

edit flag offensive delete link more

Comments

Hi @Ratslinger,

am speechless, just so brilliant, thank you!

gkick gravatar imagegkick ( 2020-09-09 06:30:55 +0100 )edit

@gkick,

You are welcome. Glad to finish the day on a good note.

Ratslinger gravatar imageRatslinger ( 2020-09-09 06:39:44 +0100 )edit
1

answered 2020-09-10 03:02:27 +0100

cpb gravatar image

updated 2020-09-11 15:41:55 +0100

i am making this post as an answer because i cannot see how to include an attachment in a comment.

@gkick i saw your previous post concerning running sums but did not respond because it was marked as solved even though it was obvious that the solution did not solve your issue.

@Ratslinger just for your information: when using HSQL embedded (the OP did not supply back end info) it's possible to solve this issue by using 2 simple queries. only 1 query is required for databases which have window or recursive functions.

as you know the trick is in assigning a unique value which can be used to identify each individual record in chronological order even when date/time values are identical. the join in the setup query enables this.

EDIT: attachment not found error. have uploaded again. C:\fakepath\New Database.odb

EDIT: 11 Sep 20

@gkick

i tested the attachment using both hsqldb 2.5.1 and postgresql 12.4 on windows 10 with LO 6.3.6.2(x64) and discovered an issue when inserting new records via the form.

'Base' did not like the table aliases so please use the code below for the output query.

i have also posted a recursive routine, hsqldb 2.x only, it will not work with embedded. the recursive routine is read only and not suitable as a data source for the form.

Base forms used for input can be volatile when the data source is a query which contains data from more than a single table.

calculating a running sum is expensive in terms of CPU and your database may eventually contain many thousands of records.

it's not as convenient but may be best to use 2 forms as you have done previously.

--cash_flow_running_sum_output.sql
select "cash_flow_t".*, "q"."running_sum"
from "cash_flow_t"
join
(
    select "q1"."id", sum( "q2"."total" ) "running_sum"
    from "cash_flow_running_sum_setup.sql" "q1"
    join "cash_flow_running_sum_setup.sql" "q2"
    on "q2"."level" <= "q1"."level"
    group by "q1"."id"
) "q"
on "q"."id" = "cash_flow_t"."id"
order by "cash_flow_t"."date", "cash_flow_t"."id"


--cash_flow_running_sum_recursive.sql
--shows daily transactions with a running total using recursion
--hsqldb 2.x and above. if using hsqldb.jar < 2.5.1 then replace 'union all' with 'union' (bug)
with recursive y("id", "debit", "credit", r) as
(
    select y.*, rownum() r
    from (select "id", "debit", "credit" from "cash_flow_t" order by "date", "id") y
),

t("id", r, "running_sum") as
(
    select "id", r, coalesce("credit", 0) - coalesce("debit", 0)
    from y
    where r = 1
    union all
    select y."id", y.r, t."running_sum" + coalesce(y."credit", 0) - coalesce(y."debit", 0)
    from t
    join y on y.r = t.r + 1
)
select c.*, t."running_sum"
from "cash_flow_t" c
join t on t."id" = c."id"
order by c."date", c."id"
edit flag offensive delete link more

Comments

@cpb,

Your sample produces a 404 Not Found error.

From previous questions (note I refer to previous in answer) the OP has stated the DB is HSQLDB v3.51.

Would much like to see you solution with '2 simple queries'.

Ratslinger gravatar imageRatslinger ( 2020-09-10 03:11:53 +0100 )edit

@cpb, hi I am using HSQL 2.5 as a backend. Attempting to download your db I end up with 404s

gkick gravatar imagegkick ( 2020-09-10 03:28:46 +0100 )edit

have replaced attachment.

cpb gravatar imagecpb ( 2020-09-10 03:43:33 +0100 )edit

@cpb,

Very good, but do not understand why you have not posted before?

Ratslinger gravatar imageRatslinger ( 2020-09-10 03:51:25 +0100 )edit

@cpb , can confirm works with HSQL 2.5 backend as well.

gkick gravatar imagegkick ( 2020-09-10 04:16:13 +0100 )edit

@Ratslinger said do not understand why you have not posted before?

initially i did not know what to make of this comment but having revisited the parent of this thread here i now understand.

the parent thread was first penned on 16 Aug and the solution was accepted on 17 Aug. i knew that the solution did not resolve the issue and revisited the thread over the following 2 or 3 days anticipating a reaction from the OP, that reaction did not materialise and i lost interest.

now i see that the OP made a further comment on 26 Aug and communication resumed between yourselves.

the problem is that when comments are posted users who visit this site such as myself are unaware that the thread is active and the thread continues to slip down the listings when it should be bumped to the top.

my answer is simply food ...(more)

cpb gravatar imagecpb ( 2020-09-10 16:15:15 +0100 )edit

@cpb

Thank you for stress testing your alternative solution in such a detailed way. Since as you say the whole thing causes a fair bit of an overhead in addition to the forms volatility I am inclined to revamp the overall design.

Either way, still need to calculate a running sum as this is used in the same form to indicate forthcoming negative balances occurring within 30 or 90 days. See edited post above.

Using 2 forms brings me back to the original question of how to synchronize the scrolling of related sub forms and or hide scrollbars.

gkick gravatar imagegkick ( 2020-09-11 21:09:32 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-08 23:08:42 +0100

Seen: 112 times

Last updated: Sep 11 '20