layout editor

Moderators: Phil Winkler, Graham Smith, Pete Tabord

layout editor

Postby pkpk » Sun Nov 09, 2008 9:06 pm

Howdy chess fans,

Now that I've got your attention, I'm having real difficulty with the layout editor. There appears to be no documentation on this. If there is documentation, a pointer would be appreciated.

I want to do something very simple. Take this DQL:

for pickup_line with boat_number = data-entry boat AND any R_pickup_header pickup_date = data-entry ship_date ;
list records
any R_variety class in groups ;
pickup_number in groups ;
nboxes : sum .
end


I want the output to look like this:
GRAPES
945555 1,920
945556 2,112
TOTAL FOR GRAPES 4,032
PEACHES
945555 208
946001 416
TOTAL FOR PEACHES 624

GRAND TOTAL 4,656


the DFD layout would look like this (I have put the fields in angle brackets):
.header
.group header
<any R_variety class>
.group header
.items
.group trailer
<pickup_number> <nboxes>
.group trailer
TOTAL for <any R_variety class> <nboxes>

.end
GRAND TOTAL <nboxes>

Now - my problems with the Ffenics layout editor amount to this:
A. There are lots of concentric rectangles on the layout screen, which undoubtedly have a well-hidden meaning.
B. There is a massive amount of empty vertical space between items that I would like to delete, but can't. In particular the
innermost of the rectangles referred to in A above is empty, but cannot be deleted, and it is an inch tall.
C. There does not seem to be any way to repeat the <any R_variety class> field both at the top of the group and at
the bottom of the group. In fact, when I move it to the bottom of its rectangle in the layout editor, it actually prints in
the middle of the group data, like this:

945555 1,920


GRAPES

945556 2,112
TOTAL 4,032

945555 208



PEACHES
946001 416
TOTAL 624

GRAND TOTAL 4,656

That's mighty confusing.

As always, any help is greatly appreciated.

Regards,
Peter Kopke
pkpk
 
Posts: 5
Joined: Thu Jul 31, 2008 7:34 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby Robert Osinski » Mon Nov 10, 2008 3:23 pm

Well Peter, the first thing you need to do is get over DOS think. What you’re attempting to do is easily doable once you understand how the Ffinics PSL layout works.

Let me start by explaining that those “concentric rectangles” are called containers. If you click on View >> Container Labels you’ll see that they correspond to the grouping you created in the PSL script. You cannot move fields between these containers.

The innermost container that you refer to is the one that would be the equivalent of the .items band in DFD. While you can’t delete it, you can resize it and move it out of the way.

To repeat the outermost group in the group summary section you need to create what’s called a “Layout Only Virtual Field”. From the objects pallet choose an edit box. Name it something like “vClass” and give it a derivation of ‘concat(“Total For “,any r_Variety class)’. This field should be placed in the same container that your group totals are.

I hope this shed some light on your dilemma.
Robert Osinski
 
Posts: 131
Joined: Mon Sep 10, 2007 3:01 pm
Location: Downers Grove IL
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby pkpk » Mon Nov 10, 2008 4:24 pm

Thanks Robert.

1. Some of the containers refuse to be shrunk to a reasonable size, even when there are no other items "in the way". -- is there some trick to this?

2. When I add the Layout only virtual field with the derivation you give, only the constant text part appears in the output: i.e., only "Totals for" appears when I use the derivation concat("totals for ", any R_variety class).

3. When I change the derivation to concat ("totals for ", R_variety class), Ffenics crashes with error message

EXITING DUE TO EXCEPTION: Access violation
Instruction Pointer: 0x005E7950
Stack Pointer: 0x0012EAF0
Last Message: 515
Last Action: 21277
UI Build: FF_00252
PRISM Build: DC_00248
Windows Version: Windows XP
Stack Info: 0x005E7950
0x005E7950
0x005E7950
0x005E7950
0x005E7950
0x005E7950
0x005E7950
0x005E7950

Ffenics has crashed a couple of times on me in a few hours of use. Is this commonplace with the 1.24 version?

Regards,
Peter Kopke
pkpk
 
Posts: 5
Joined: Thu Jul 31, 2008 7:34 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby Phil Winkler » Mon Nov 10, 2008 4:34 pm

Hi, Peter,

Your query structure is very odd and is likely causing you the problems with GPfing, etc. "DOS think" simply doesn't work in the windows products and the fact that your query did work in DFD just points out how forgiving it was. I think I would bring the Class field into the main table to eliminate that "any....in groups".

When I first create or later make large mods to the Bod I usually select Field per line as the layout. This gives you a great picture of your data model and the logic of the containers.

But, are you having fun with the new software? :wink:
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby pkpk » Mon Nov 10, 2008 4:48 pm

Phil,

I was hoping to avoid massive denormalization in Ffenics. If grouping on a field in a related table is weird, it's only because the entire "join" concept is not expressible in DQL. It seems that the Query by Model editor might be used to express such natural joins, but I found it opaque, and there is apparently no documentation for that either.

Ffenics is a bit fun, but also a bit frustrating.

Regards,
Peter Kopke
pkpk
 
Posts: 5
Joined: Thu Jul 31, 2008 7:34 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby Phil Winkler » Mon Nov 10, 2008 5:55 pm

Peter,

The "any relation field in groups" usually indicates to me the coder is starting in the wrong place as it is simply illogical. Why not start in that table and select records with "count of related subform > 0"??

Much simpler, more logical....
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby pkpk » Mon Nov 10, 2008 7:22 pm

Phil,

There are numerous occasions when one cannot run the report off the related form. For example: suppose we want to group on the pickup date also, but that is in another related form pickup_header.

for pickup_line ;
list records
any R_pickup_header date in groups ;
any R_variety class in groups ;
boxes : item sum .
end

The point here is that using DQL we must choose one table on which the "For" is based, whereas in SQL we don't:

select pickup_header.date, variety.class, SUM(pickup_line.boxes) from pickup_header, pickup_line, variety where
pickup_header.pickup_id = pickup_line.pickup_id AND pickup_line.variety_id = variety.variety_id GROUP BY pickup_header.date, variety.class;

Regards,
Peter Kopke
pkpk
 
Posts: 5
Joined: Thu Jul 31, 2008 7:34 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: layout editor

Postby Graham Smith » Mon Nov 10, 2008 7:47 pm

Code: Select all
any RelatedForm in groups

This actually works somewhat better in Ffenics than it did in DFD, but it's still a less than optimal solution as the first grouping should really dictate what form the query starts in. The one main caveat is that you cannot reliably group on a virtual field in a related form.

As to the containers issue. If you cannot shrink a container down that doesn't have anything in it, then just resize it using using the "Same Size" button. Click on the container then on a field that has been reduced in size and select Same Size - Both Sizes.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: layout editor

Postby Adrian Jones » Wed Nov 12, 2008 11:45 am

Peter,

This issue of grouping is something that has never been properly addressed since DfW was first released, and is a constant source of confusion, esp. for the DOS-familiar.

I personally think that a better way needs to be added to the product to allow these very common types of reports to be written in a more intuitive fashion. But I do appreciate that simply thinking this is a million times easier than Pete's job of implementing it!

Anyway, let me start with a couple of things.

First, I think your concat code GPFs because you are missing 'any' before the relationship.

Second, in DOS you are used to writing a procedure and then changing the structure of it in the layout. In Dfw/FF, you need to get the structure right in the query part.

Now for the odd stuff. When you list in groups, you effectively split a table into two tables: a parent that contains the unique grouped value, and a child that contains all the rows that belong to that group.

For the type of report you want here, you really are looking to add a (virtual) field to the parent table, that summarizes some of the contents of the child. You do not want the child rows to appear.

So:
Code: Select all
For MyTable ;
  list records
    GroupedField in groups ;
    ValuesToSummarize : sum .

Creates form and record container sets, with no fields in the second set (since you have not asked for the items to appear).

But if you code:
Code: Select all
For MyTable ;
  list records
    SomeMeansToSummarizeThoseFields : item sum ;
    GroupedField in groups .

You will find that you get only one container set, plus overall summary fields for the totals for the group and the total overall. In fact, I think you'll agree that (apart from the not-so-minor issue of what to put in the place of 'SomeMeansToSummarizeThoseFields') the layout is virtually good to go -- you just need to swap the order of the grouped value and the summary.

Now, what I'm about to suggest is not going to win prizes for efficiency, but it will produce your report as needed:
Code: Select all
For MyTable
  list records
    sum of MyTable named "SameGroupedValue" with ( GroupedValue = MyTable GroupedValue ) FieldToSum : item sum ;
    GroupedValue in groups .

This is inefficient because the adhoc relationship forces a re-read of the table ... but at least it will give the layout/body you want, more or less: no extra containers and having to scrunch things up.

(It will read better as well when you use real fields and tables, rather than the tautologous examples above.)

Let move this a stage further, to include two levels of grouping...
Code: Select all
For MyTable ;
  list records
    OuterFieldToGroup in groups named "OuterGrp" ;
    sum of MyTable named "SameOuterGroupedValue" with ( OuterFieldToGroup = OuterGrp OuterFieldToGroup and InnerFieldToGroup = OuterGroup InnerFieldToGroup )
FieldToSum : item sum ;
    InnerfieldToGroup in groups .

Note that:

1. I am using the named operator after the groups (something you couldn't do in DOS)
2. The adhoc relationship references my renamed group
3. I have to effectively restate the double grouping as a relationship (not pretty -- though I could also create a predefined rel for this purpose)

Does this make sense? If not, try this article I wrote several years ago:

http://www.dataease.com/downloads/Nov%202000%20Pages%2009-12.pdf

Ideally, I'd like to be able to code:
Code: Select all
For MyTable
  list records
    sum of ValueGrp MyField : item sum ;
    GroupedValue in groups named "ValueGrp" .

However, you get a parser error, probably because the script is parsed from top to bottom, and so the ValueGrp is yet defined.

Now, you can also do this:
Code: Select all
For MyTable ;
  list records
    GroupedValue in groups named"ValueGrp" .
  list records
    count of GroupedValue : item sum .

Which for me produces the most elegant solution -- this layout is good-to-go. However, this only works with count of, is probably a coding quirk, and only works with the top level of grouping.

Incidentally, the DOS-familiar will always head off in the direction of a procedure. You should explore what a report document offers as well ... though unfortunately, in this case, you are stuck with similiar issues.

Finally for the moment, if the performance my suggestion above yields is unbearable, you might have to consider posting data to a summary table and reporting from there. But that is a whole other email!
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: layout editor

Postby Pete Tabord » Wed Nov 12, 2008 6:49 pm

Not to rake over old ground, but grouping is simple, it just isn't anything like DFD!

When you declare a group, you in fact create a new form with a one-to-many relationship with the items in each group. It's invaluable, when learning, to look at the data model to see what is going on.

To avoid quirks of behavior, you should always declare your groups as the first items in your list records, followed by any additional ordering (which was always the requirement in DFD anyway).

If you separate out the any and the in groups you should get what you want - try a structure like:

Code: Select all
for MEMBERS ;
  list records
    ADULT FEES  in groups named "grp" ;
    any grp ADULT FEES : sum .
end


Once you get the hang of it, its possible to write very powerful queries that generate their own layout structure somewhere near right first time - you just then have to pretty it up, which always takes longer under Windows - largely because there are many more things you can do...
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 

Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron