Getting More Out of Filtered Portals: 6. Hierarchical Portal

[comment]

Just a basic sketch with (almost) no frills.

Compare previous implementations:

Excelisys Hierarchical Portals:

http://www.excelisys.com/tips-tricks-filemaker-downloads.php

Mikhail Edoshin:

http://edoshin.skeletonkey.com/

HierarchicalPortal11.zip

[Ocean West]

elegant as usual comment! :)

[Søren Dyhr]

Nice one indeed!

--sd

[Reinder]

One word: WOW!

[comment]

Here are some modifications provided by mr_vodka

• the global field has been replaced by a variable (though this requires a screen refresh when expanding/collapsing items);

• the item label is a layout variable instead of a calculation field;

• the disclosure triangle is now a part of the item label, so it too indents according to the item's level;

• indent uses a tab character instead of spaces, allowing control of the desired indent on layout level.

HierarchicalPortal11Mod.zip

Edited June 28, 2010 by Guest
added * #4

[LaRetta]

Well, I've been playing with this and even praising it to others but it's time I said so here ... the techniques presented by you and John (Mr_Vodka) are incredibly exciting and powerful. Using conditional format to declare (and update) variables absolutely ROCKS.

I want to thank you both for your contribution and for sharing the ideas!!!

[SY Lim]

I was trying to replicate the hierarchy (I am new to merge variables) but still failed to do so. What is the issue here?

Rgds

SY Lim

Hypercube_Sales_Management_System_3_.zip

[comment]

"put me behind the portal" should be read as "behind the top row of the portal".

[lance123]

Hi Comment,

This is very cool! just been trying to understand it...Where the heck does the <<$display>> variable get set?? I am sure I will go 'doh' when I find out :

Thanks,

Lance

[comment]

Conditional formatting of the "I declare variables..." text object.

[lance123]

doh! It was right in front of me! Thanks Comment

[Daniel Shanahan]

Thank you, Comment, for this file. It is very nice. I like sorting the hierarchy on a number field (cSortfield) rather than a text field as it is not necessary to pad IDs with "0"s. (Nonetheless, I still appreciate Andrew Persons file and especially the accompanying white paper.)

A couple of questions come to mind:

1. Comment, how many records and levels can your version support? This is a theoretical question as I'm sure that a user would not want to click through a lot of levels. Persons' version can support a million records at 15 levels or 100 million records at 12 levels (White Paper: Hierarchical Portals in FileMaker Pro 7 - 10 by Andrew Persons, p. 1).

2. Persons' record/levels is based on FileMaker's indexing. He writes, "FileMaker indexes up to 110 characters of each line of a text field..." (p. 1). I am a bit confused by this. According to FileMaker, in FMP 11, a text field "index is based on the first 100 characters of each word or value". This is also true for FMP 7 .

Does this mean that an individual id can have 100 characters (e.g. 99 "0"s and a "1")? With a capacity for 1 billion characters per field ("limited by available memory), this would certainly make the record/level higher than 1m/15 levels or 100m/12 levels, would it not? Or, am I misunderstanding FileMaker text indexing and/or records/levels in hierarchical portals?

[comment]

Ahm... in the Persons' file, what do you see when you click into the field used for sorting and select Insert > From Index… ?

[Daniel Shanahan]

Thank you for your reply, Comment.

The field used for sorting in Persons' file is PathID. When I enter this field I am unable to choose Insert>From Index... However, when I click into the ParentPathID I am able to choose Insert>From Index... and this is what I see (sometimes I have trouble uploading .png and .zip files; not sure why).

Ahm...

As soon as I read this I thought, "Well, I must have missed something obvious." So, I did a little more research. I created a FMP file and tried to hit the index limit but failed - perhaps due to the small size of the file (only a handful of records). In the TextID field I expected the Insert from Index to only insert the first 100 characters of a word (digits, in this case). Yet, I was able to paste in 2,448 characters.

I also created a .pdf to help me visualize my question. The .pdf and the .fp7 (zipped) are attached.

So, after several hours of testing, I'm still a bit perplexed - perhaps more so now. I'm still curious about the number of records and levels that Comments version supports. If I understand FMP text indexing correctly (bit of an assumption here) at 100 characters per word with no expressed limit on the number of words, than it seems like the number of records and levels would be very, very large.

FMIndexing.pdf

IndexingTest.fp7.zip

[comment]

Well, the point is that unstored calculations are not indexed - so I am not sure why my method should be affected by the indexing limitation.

In practical terms, the file uses unstored calculations for both sorting and filtering, so I would expect that with a large number of records it would slow down to unacceptable speeds long before reaching the kind of numbers you mention.

[Daniel Shanahan]

Well, the point is that unstored calculations are not indexed...

Ah, that's the obvious thing that I missed.

Thanks for the reply, comment.

[Genx]

Developers really are the main thing keeping FileMaker advancing.

FileMaker Inc is lucky people like you and other's share moments of ingenuity like this.

Powerful technique - thanks Comment and Mr_Vodka.

[imoree]

Just a basic sketch with (almost) no frills.

Compare previous implementations:

Excelisys Hierarchical Portals:

http://www.excelisys...r-downloads.php

Mikhail Edoshin:

http://edoshin.skeletonkey.com/

I know it has been a long time, but can you explain this Comment?

Case ( ParentID ;

Substitute ( 10^ValueCount ( cAncestorIDs ) - 1 ; "9" ; " " ) ) & ItemName

thanks,

*Looks to me like it is finding the 1st name ( occurence on the portal) , no matter what i change in calc, i get

"Africa"

Am i correct ?

-ian

Edited March 8, 2012 by imoree

[comment]

It indents the ItemName by putting spaces in front of it. The number of spaces is given by the number of previous "generations".

[imoree]

This file has been so Simple, but so darn awesome. What an easy way to do a hierarchical List using a portal! i have looked at other files, which have boatloads of TO's and Custom Functions,etc So convoluted.

How on Earth do you do it? i am in amazement by You, Comment.. Simplicity is elegance!

thanks for sharing this again...

-ian

[mselbach]

the update of the list gets much more smoother if you replace the "refresh window - flush cache" script-step by a simple setField(Viewer::SerialID, Viewer::SerialID+1) script-step.

thanks from me too,

markus

[imoree]

the update of the list gets much more smoother if you replace the "refresh window - flush cache" script-step by a simple setField(Viewer::SerialID, Viewer::SerialID+1) script-step.

thanks from me too,

markus

That does not work for me at all..

[bruceR]

Ian, it is a valid technique and actually a valuable basic method for triggering refreshes.

So if you were not able to make it work, you might state exactly how you have used the technique or explore what you have done incorrectly while attempting to implement it.

[imoree]

So if you were not able to make it work, you might state exactly how you have used the technique or explore what you have done incorrectly while attempting to implement it.

You are correct> changed my SerialID from AutoEnter serial to INDexed and it works beautifully now.

thank you

[bruceR]

That is not the issue. It was already auto-enter Serial, indexed in the original example. But it can even be a global field. The relationship from Viewer to Items is a cartesian (X) relation so the index doesn't matter.

[imoree]

Hierarchy_test.fp7.zip

That is not the issue. It was already auto-enter Serial, indexed in the original example. But it can even be a global field. The relationship from Viewer to Items is a cartesian (X) relation so the index doesn't matter.

I have attached a file that does not iterate the SerialID field in the Viewer. If you change it to indexed only OR CREATE a new record while in Viewer Table, it won't expand the list. The file attached shows no movement like it should.

-i

[bruceR]

Your file contains two basic errors which you introduced and illustrates that the original tip is correct if the simple instructions are followed.

First of all, your script does not actually set the SerialID field.

Secondly, the relationship is broken.

Apparently you may have deleted and then re-created the serialID field; but did not change the script and relation to use the new field.

[imoree]

Your file contains two basic errors which you introduced and illustrates that the original tip is correct if the simple instructions are followed.

First of all, your script does not actually set the SerialID field.

Secondly, the relationship is broken.

Apparently you may have deleted and then re-created the serialID field; but did not change the script and relation to use the new field.

Yep.. I feel like a real dummy now.. thanks for that..

[Mr. Weaver]

Can the list be made to sort by more criteria than the key fields in cSortField?

For example: I have a price list table where there are menu records and item records and this works fine. I would like to be able to also sort on a "position" field in the event that an item needs to change position in the price list.

I have added a field that holds the position value of each menu and item record but I'm not able to get the sorting to work properly; I keep running around in circles. Can someone nudge me in the right direction?

Thanks!

- Brian

[comment]

It's not exactly trivial. If you have a unique position value for each item, you could probably use that instead of ItemID in the cSortfield calculation.

[Andrey Popov]

Hallo to everybody

I'm new to FM, and maybe done somthing wrong, but:

I tried to implement this solution in test file, but encounted problem with performance - tried to put about 450 records to the hierarchi and every folder is started to open within about 5-10 seconds depending on the amount of records inside. I'm afraid that if the amount of records will be about 1-10K it will load for hours...

Did anybody else experiance the same problem? Maybe there is a solution of the performance problem in portal?

Plz help!)

P.S. Sory for my English)

[comment]

As discussed earlier, filtered portals - in general, not just this technique - are not suitable for a large amount of records. IMHO, finding one record out of a thousand or more in a hierarchical portal is not so convenient for the user anyway.

[Andrey Popov]

As discussed earlier, filtered portals - in general, not just this technique - are not suitable for a large amount of records. IMHO, finding one record out of a thousand or more in a hierarchical portal is not so convenient for the user anyway.

So you say that there is no "good working" way to build a hierarchical navigation in FM? I dont agree with you about "is not so convenient for the user anyway", because at this time we use SQL build database with about 10 000 records in it and it structured the way it is very easy to find...something like: category>sybcategory>10-20 goods...

I realy want to use FM in our company, but hierarchi is the main problem for now...need to find solution)

[comment]

So you say that there is no "good working" way to build a hierarchical navigation in FM?

No, that's certainly NOT what I am saying; I am saying that in order to build a hierarchical display with this amount of records, the solution needs to be based on relationships - i.e. working at the data level - instead of filtered portals that work on the layout level.

This is true no matter what method you choose to display the hierarchy: it could be a hierarchical portal, or a set of 3 side-by-side portals, or conditional value lists or something else altogether.

we use SQL build database with about 10 000 records in it

Let's say you have 10 categories with 10 subcategories each, and each subcategory holds 20 items; that's only 2,000 items. In order to select an item, the hierarchical portal needs to display 40 rows in total: 10 categories, 10 subcategories and 20 items. Speaking as a user, I don't think I would find such arrangement convenient. But that's just my opinion.

[Andrey Popov]

No, that's certainly NOT what I am saying; I am saying that in order to build a hierarchical display with this amount of records, the solution needs to be based on relationships - i.e. working at the data level - instead of filtered portals that work on the layout level.

This is true no matter what method you choose to display the hierarchy: it could be a hierarchical portal, or a set of 3 side-by-side portals, or conditional value lists or something else altogether.

I see your experiance is much reacher than mine) Maybe you can give some advice where i can see example of the "best solution" or read something so i can try to use the right way of doing that? I already read tons of forums and found only two solutions - this one and the one of excelisys dot com...didn't try the second one so far, but as I understand the algorithm is the same)