Home » Developer & Programmer » Forms » group column in data block
group column in data block [message #355983] Tue, 28 October 2008 21:27 Go to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
guys can i dynamically group columns in a data block urgent!!!!!
Re: group column in data block [message #356014 is a reply to message #355983] Wed, 29 October 2008 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know whether you can do that urgently, but - perhaps a somewhat slower approach might help.

What do you call "dynamically grouping columns"?
Re: group column in data block [message #356217 is a reply to message #355983] Thu, 30 October 2008 00:27 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
from an insurance table... which has let say

assured_name / premium_payments / and date_payment are the columns

ex. sample data....

edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08

then the user will input 2 dates which will serve as the data range.... so if the user will input 01-02-07 and 01-10-08

will display all records between the two dates but the premium_payments must be sum up and assured name must be grouped so output will be

edward 700
john 100
mark 200

this is easy in report but in forms how can i do this....
i already than this in report but the client also want to have preview of data in form....


Re: group column in data block [message #356221 is a reply to message #355983] Thu, 30 October 2008 00:30 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
i have done this in report already...

the sql is


select assured_name , sum(premium_payments) as premium_payments from table where date_payment between :date1_param and :date2_param group by assured_name order by assured_name

and this worked properly but the client wants it to be previewed first in the form....
Re: group column in data block [message #356224 is a reply to message #356221] Thu, 30 October 2008 00:35 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In that case, a VIEW (and form's data block based on this view) might be a solution.
Re: group column in data block [message #356242 is a reply to message #355983] Thu, 30 October 2008 01:22 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
i already considered that but how will you group the assured name in view when you dont know yet the data range that will be entered by the user....


[Updated on: Thu, 30 October 2008 01:23]

Report message to a moderator

Re: group column in data block [message #356243 is a reply to message #356242] Thu, 30 October 2008 01:28 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Data range can be used in the DEFAULT WHERE clause set by the SET_BLOCK_PROPERTY built-in.
Re: group column in data block [message #356247 is a reply to message #355983] Thu, 30 October 2008 01:33 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
so examine the table above ..... then how will grouped that in a view where ...

edward 400 01-02-07
edward 300 01-02-08

so if you group this in a view

this will be the output

edward 700

the problem is what if the user only input this date range
01-02-07 and 12-12-07 so the output must only be

edward 400

that will be the problem if you will create a view because you dont know yet the input of the user....

Re: group column in data block [message #356249 is a reply to message #355983] Thu, 30 October 2008 01:35 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
did you get my point?
Re: group column in data block [message #356266 is a reply to message #356249] Thu, 30 October 2008 01:56 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

What your problem really is? It has been said that you can define your restriction on block's where clause. Have you tried using view?

Also, your block data can be from query should you don't want to create a view.
Re: group column in data block [message #356268 is a reply to message #355983] Thu, 30 October 2008 02:11 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
ok lets start with creating a view .... if you will group the records above in a view using assured name and sum(premium payments) you must not include the date payment ... because if you include the date it will not be grouped properly ...

so how in this earth are you going to search for date range...


so if your code is this.. in view

select assured_name , sum(premium_payments) as premium_payments from table group by assured_name

then the output will be...


edward 700
john 100
mark 200



so there is no way now that the user will be able to enter the date range.. if we will not include the date

.. but if you include date in our view the output will not be grouped properly it will be like this.


edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08

just the same as the table ...




Re: group column in data block [message #356269 is a reply to message #355983] Thu, 30 October 2008 02:13 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
so that's the problem... yes we can create a view.. but remember the user must be able to enter date ranges date 1 and date 2

Re: group column in data block [message #356271 is a reply to message #355983] Thu, 30 October 2008 02:16 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
if im going to use the where clause property for the date... this will be the output.....

let say date 1 and date 2 are..... 01-02-06 to 01-02-09


edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08

what the client wants is..... the output should be grouped... like this...

edward 700
john 100
mark 200

but remember if the date ranges are..... 01-01-08 to 01-02-09 it must be like this....


edward 300
john 100
mark 200

why? because the first record is not covered by the date range gets?
Re: group column in data block [message #356274 is a reply to message #355983] Thu, 30 October 2008 02:29 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
actually i already found the solution for this just an hour ago... by creating a temporary table... when the user clicks the search button the temp table will be truncated first and populated by data based on the date ranges.......


i just want to know if there are other possible ways to solve this...

and i guess this will be not possible in using view...that is in my opinion but if you have ways to do this using view i want to know...

[Updated on: Thu, 30 October 2008 02:30]

Report message to a moderator

Re: group column in data block [message #356276 is a reply to message #355983] Thu, 30 October 2008 02:37 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
wency i have a strong feeling you are pagie in pex hehehe....
Re: group column in data block [message #356277 is a reply to message #356276] Thu, 30 October 2008 02:54 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

In view? Search for materialized view, but I think it still won't do. For that problem where you have to channel your data, the best thing is to have a temporary table which you have did.

Another thing to do is to create a cursor, and populate your block with that data from cursor. I think your block here is not a DB block.
Re: group column in data block [message #356492 is a reply to message #355983] Thu, 30 October 2008 22:14 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
wency are you pagie in pex?...
Re: group column in data block [message #356528 is a reply to message #356492] Fri, 31 October 2008 00:29 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have no idea what a "pagie in pex" might be; however, would you (xpact83) please discuss it with Wency via private messaging system, or in Community hangout forum, or elsewhere? Now it's been the third time you insist on the same question (obviously, no answer so far).
Previous Topic: Allignment problem in forms
Next Topic: Need to allow user to update data
Goto Forum:
  


Current Time: Fri Feb 07 17:44:58 CST 2025