Sub-summarys within portals pt I

There are a few ways to achieve a ‘sub summary’ look with in a portal. The simplest is to hide repeating data with conditional formatting. Using the getnthrecord to work out if you are on the first or last record and showing relevant data. If you wish to show a sum then you will need to use a self join the using the parent key and whatever field you are sorting by.

If you want to go for completing free form sub summarys then life gets a little trickier. In this example I am going to look at the quoting and invoicing system for a client who wanted to play around with the order of lines with in the quote and insert totals wherever they felt like.

Arbitrary Sorting Of Portals

The first thing we need to do is allow the user to sort the portal rows in an arbitrary fashion. The UI for this is simply an up and down arrow on the left of the portal row. If the user clicks up the line moves up, click down and the line moves down. Simplezzzz. Custom formatting is used to show or hide an overlay with summary information.

Underneath the hood things are a little more complex but not infinitely so. The portal is sorted by a number field which auto enters a serial number. Effectively this means every new row is inserted at the bottom. When a user clicks an arrow a simple script increases or decreases the number in the sort field to move the row up or down. The theory is to move up a row we take the mean of the two rows above, to move down take the mean of the two records below:

If we want to move the account management line up one row we press the up arrow and, as if by magic, the row moves up:

The script that performs the movement is relatively simple; the heavy lifting is all done in a single calc:

The interesting part of the script is the calc:

Let([
n = Get(ActivePortalRowNumber);
c = Count(Quote_QUOTELINE::__kp_QUOTELINE);
v = Quote_QUOTELINE::d_Sort;
s = Get(ScriptParameter)
];

Case(
// This quote has been converted. No changes allowed.

QUOTE::d_ALL_Convert_To_Job_f = 1; v;

// Move up by a single portal row

s = -1;

Case(

n = 1; v;
n = 2; GetNthRecord(Quote_QUOTELINE::d_Sort ; 1)/2;
n ≥ 3; (GetNthRecord(Quote_QUOTELINE::d_Sort ; n – 1) + GetNthRecord(Quote_QUOTELINE::d_Sort ; n – 2)) / 2
);

// Move down by a single portal row

s = 1;

Case(

n = c; v;
n = c-1; GetNthRecord(Quote_QUOTELINE::d_Sort ; c) + 1;
n ≤ c-2; (GetNthRecord(Quote_QUOTELINE::d_Sort ; n + 1) + GetNthRecord(Quote_QUOTELINE::d_Sort ; n + 2)) / 2
);

// No script parameter == no change
v
)
)

The first case deals with business logic,(in this case if the quote has been converted to a job leave the line order alone), NULL script parameters and the number of records to move up and down. The above calc is the simplified version only moving a row up or down a row at a time, the origional version moves up or down by the value of the script parameter but I might leave explaining that one to another day. Probably after a lot more coffee; I am not sure I remember how it works.

Lets look at moving up by a single portal row:

Case(

n = 1; v;
n = 2; GetNthRecord(Quote_QUOTELINE::d_Sort ; 1)/2;
n ≥ 3; (GetNthRecord(Quote_QUOTELINE::d_Sort ; n – 1) + GetNthRecord(Quote_QUOTELINE::d_Sort ; n – 2)) / 2

);

n = 1 means we are on the first portal row, therefore the sort value remains the same.

n = 2 means we are on the second record. Using getnthrecord we get the d_sort value from the first portal row. Dividing it by 2 will always result in a smaller number then the current record thus moving our record up.

n ≥ 3 we are in the portal where we can freely move up with no risk of running out of head room. We get the value of d_sort from the two records above and take the mean.

The biggest disadvantage of the mean approach is the rapidly increasing length of the resultant number. However the users do not interact with the sort value directly and FileMakers default resolution is accurate enough to deal a significant amount of movement.

In part II we are going to look at summarising the data

 

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

More Posts - Website

Leave a Reply