Display All Values Except For One

[dmontano]

I have a value list that pulls values from a table's field. I want to show all the values (as normal) - except for one of the values. The table I am drawing the values from has one record called "Root". I do not want "Root" to show up in the drop down list. How can this be done? (I have a record called "Root" in this table as it is used to control a hierarchy within that table.)

Any help appreciated, thanks.

[comment]

You could define a calculation field =

Case ( Value ≠ "Root" ; Value )

and use this to define the value list.

[dmontano]

Hi Comment,

Thanks for the help! It worked. I never thought of using the Case function like this.

Which brings up another question - that is related.

I am trying to hide this one particular record's Name of "root" because I am using the Root record to support the function of the hierarchy only, but need the other values in a list so a User can assign what TYPE of work effort they are creating. So, for example, I have records in a table like this:

Table = WORK EFFORT TYPE

Record 1: Name = Root, Level = 0

Record 2: Name = Portfolio, Level = 1

Record 3: Name = Campaign, Level = 2

Record 4: Name = Program, Level = 3

Record 4: Name = Project, Level = 4

The Level is used as a mechanism to control the hierarchy. It is not entered by a user, rather calculated by the absence or presence of a parent key in the record (Basically, if parent key is empty - then level = 0; else, parent level + 1). Root should never change as it provides the basis for the hierarchy to "sort" itself out.

Is this a valid method of building a hierarchy - with the overhead of either "hiding" the root records name or leaving the root record name blank. The latter (leaving root blank) poses a problem with validation (field not empty).

Now that I can see how you accomplished eliminating "root" from the list - I need to apply this to the value list drawing the "key" values instead of "name" field in order to use it in a relationship. In either case, I would have to hard-code the value I do not want to show (either hard-code the name "root", or hard-code the primary key). If there was a way to NOT hard-code this - I think it would be better.

So, is it possible to build a calc that prevents hard-coding? Such as… anytime a record has a Level = 0 value, use that records' primary key as the value to ignore (in the case of wanting a value list of keys for relationship building)? I tried the calc shown below to no success (value list shows one primary key only). I suspect because the Level field is an unstored calcualtion. I run in to this "unstored calculations" problem a lot - and have not uncovered how other people have gotten around it.

Case (

work_effort_type_hierarchy_level ≠ 0 ; _work_effort_type_pk

)

Any ideas?

[dmontano]

Okay,

I think I figured this out:

For use as a drop down to set the foreign key in another table:

*********************************************

Case (

IsEmpty ( _work_effort_type_parent_fk ) ; "" ;

_work_effort_type_pk

)

*********************************************

To "DISPLAY" the Names in the drop down:

*********************************************

Case (

IsEmpty ( _work_effort_type_parent_fk ) ; "" ;

_work_effort_type_name

)

*********************************************

This seems to work. Not sure if this is a hack and will cause problems somewhere else. I guess since both fields used in the calc. are NOT referencing unstored calculations - the list will display.

What is the advantage to using the "Case(" function instead of "If(" function? My best guess is in case another condition pops up that you want to test for - you simply add another line? Or is this just a way of always using Case( instead of If( as best practice?

[comment]

I suspect because the Level field is an unstored calcualtion. I run in to this "unstored calculations" problem a lot - and have not uncovered how other people have gotten around it.

How about going directly to the "root" of being a root?

Case ( not IsEmpty ( ParentKey ) ; _work_effort_type_pk )

BTW, what would happen if you deleted the root record?

[dmontano]

Hi Comment,

Thanks for responding. I am glad you suggested that calc as that's what I started to realize: The definition of the ROOT record is the one that doesn't have a parent - so I used that in the calc.

That got me thinking just as you did - what happens if root got deleted. I just tried it and... nothing. At least in the minute or two I played around with it - seems like all is fine. Root is gone - all subsequent records are fine. Why does this seem like it will be a problem? For the life of me I can not remember where I had read, explored, and decided that I need root and I need "0" for the root level.

I confess wholeheartedly that a recursive hierarchy is not simple for me. I also know there are different flavors to boot. So my strategy was to take a concrete business need and model it and try and implement it. I lean on the side of believing that this "TYPE" table will NOT be available to a normal user to edit, but rather available to a User assigned to maintaining the hierarchy in this particular area of the business. For example, normal data-entry users would be able to enter transactional data in say an invoicing system - but they would not be able to edit the companies chart of accounts which is used by the invoicing system. Chart of Accounts are edited and maintained by the Accounting department. Not sure if that makes any sense. In any event, your question prompts investigating the real-world use and what if the record got deleted, or also, what happens if a record is created and a Parent is not selected creating another "root"? I know these are issues I have to think through - how big - not sure.

To compound this - I struggle mightily with FileMaker validation and that's frustrating enough. The hierarchy itself has to allow a parent to NOT be assigned - otherwise there is no root. I do not know if this is what I was trying to contend with or not. Maybe I was thinking that while the values of everything above root could be changed by a user - I needed one value that did not change - root. I may have just created a solution where a problem did not exist - or a solution that does not solve the real problem. I am ALL ears.

If I could lock one record "root" in a table from being edited - that would solve root never being deleted. Once again, maybe it is not a problem?

[comment]

In my view, the table itself is the root - so an actual root record is redundant. That's why I asked if it serves any other purpose (besides of creating the above problem).

IMHO, the parent key field should be allowed to be empty. This would enable the user in charge of the hierarchy to add a new top-level category. However, if you want to force such choice to be a deliberate one, you must give them the option to select "root" as the parent key - and now you are back to the starting point... :D

[dmontano]

Thanks Comment.

Since this TYPE table really needs to be managed by a User who is managing the hierarchy - perhaps what I have is sufficient. With your help I have been able to create both the drop down that uses the key - but shows the name - without displaying "root". Since normal use only requires a User to select a value from the list - this should be fine.

Thanks again!

[dmontano]

Given how the value lists is derived via a calculation - how can I sort the drop down presented to Users by the hierarchy level. The "hierarchy level" field exists - but it is an unstored calculation.

I can sort a portal of these records based on the hierarchy level field - but can not see how one would sort the values in a drop down list.

Is this possible? If so - where to I start? I do not think there is a "sort" function in the calculation engine that I can add to the original calculation, so would I do this through a relationship?

Thanks in advance.

[comment]

LOL, this was just a few minutes ago:

http://fmforums.com/forum/topic/75395-sorted-value-list-needing-both-columns/

[dmontano]

I too was afraid of that. I really do not want to add a pop-up window.

Too bad their was not a pop-up selection portal feature in FileMaker. I think that would solve the problem.

Options for a field would be:

Pop-up

Drop-down

Pop-up selection portal

etc.

Just my 2 cents.

Thanks again Comment.

[David Jondreau]

There are alternatives to a pop up window.

You could, for example, put the field on a "hidden" tab control and when the user clicks into the field, it brings another tab panel into view, one that has a selection portal.

[David Jondreau]

All right, I think I've solved the custom sort issue for value lists drawing from a field.

It's not super helpful for the OP, since he wants to use an unstored calc field which can't be used in a value list, but other than that, I think it's a solid technique.

CustomSort.fp7.zip

Admin/(Null) will get you into the demo file.

PS: It may be unclear, but you can replace "Sort Order" in the Sort Calculation with a calculation. Say, you want to sort descending by year and your max year is 2011. Then replace Sort Order with Abs(Year-(2011+1)). Modified file attached. CustomSort2.fp7.zip

[comment]

LOL, that is a creative hack. You could probably make up a custom function to map the rank numbers to non-printing chars. Or perhaps prefix as many zero-width spaces as the rank number (this might depend on the platform, font and drop-down vs. popup).

[dmontano]

Okay - I take back my previous response that nothing happens when you delete the root. When root is deleted - the record that "had" root as its parent still holds the key value of root.

Since the hierarchy positioning or leveling hinges on the calc that checks for parent existence - deleting a record poses a problem. The calc. checks for existence of a value in the parent key field - the child record of root still thinks there is a parent record when root is deleted. This results in the child retaining a parent key value and its hierarchy level does not change.

I recall discovering this some months ago and posted the question here at FM Forums. I had cobbled a script together to find all children of the root that is to be deleted - and replaced their parent value with a null. At least this would clear the problem of the calculation thinking there is a parent. Not this solves the problems associated with creation or deletion of records in the hierarchy - but clearly the parent value remaining is in fact not true from the perspective of the calculation.

So the question is how to manage the hierarchy when a record is added, deleted, or move elsewhere within the hierarchy. I am speculating that this points toward figuring out how to "cut" clippings, and either "graft" them elsewhere or delete and rearrange the hierarchy?

Am I on the right path?

[comment]

When root is deleted - the record that "had" root as its parent still holds the key value of root.

It's my fault for confusing the issue somewhat. Instead of asking what would happen if you deleted the root record, I should have asked what would happen if there was no root record to begin with. IOW, instead of a single "root" record (e.g. Supreme Command) there would be several "top-level" records (e.g. Army, Navy and Air Force). This is a question of data structure, not data integrity.

Concerning data integrity, clearly you cannot be allowed to delete a record that has children. Other than that, assigning a parent as a child to another parent affects the entire branch.

[David Jondreau]

LOL, that is a creative hack. You could probably make up a custom function to map the rank numbers to non-printing chars. Or perhaps prefix as many zero-width spaces as the rank number (this might depend on the platform, font and drop-down vs. popup).

I've cooked up these CFs. Do you see any problems / optimizations? I thought about combining them into one function with a Asc/Desc parameter, but Asc doesn't require the maxValue parameter so I made two.

Thanks,

DJ

/* SortOrderAsc ( order ) by David Jondreau, www.wingforward.net.

This custom function will produce a set of non-printing characters to be pre-pended to a field to allow custom value list sorting. The value list needs to sort by Unicode.

This function sorts values in ascending order. If you wish to sort descending, please use SortOrderDesc().

The parameter "order" is the order in which the value should be sorted by.

Thanks to Michael Horak for suggestions.*/

Let([

num = GetAsNumber ( order );

ch = Char ( 65279 );

chars = Filter ( order ; ch );

nextNum = ( num - 1) ;

recurse = ch & chars & nextNum;

output = Case( nextNum ≠ 0 ; SortOrderAsc ( recurse ) ; ch & chars )

];

output

)

/* SortOrderDesc ( order ; maxValue ) by David Jondreau, www.wingforward.net.

This custom function will produce a set of non-printing characters to be pre-pended to a field to allow custom value list sorting. The value list needs to sort by Unicode.

This sorts values in descending order. If you wish to sort asscending, please use SortOrderAsc ().

The parameter order is the order in which the value should be sorted by. maxValue is the highest number an order can be.

Thanks to Michael Horak (comment on FMForums) for suggestions.*/

Let([

mv = maxValue;

num = GetAsNumber ( order );

ch = Char ( 65279 );

chars = Filter ( order ; ch );

nextNum = ( num + 1) ;

recurse = ch & chars & nextNum;

output = Case( nextNum ≠ mv ; SortOrderDesc ( recurse ; mv ); ch & chars )

];

output

)

[comment]

How about =

Substitute ( 10^order - 1 ; "9" ; Char ( 65279 ) )

for the ascending? This would work for up to 404 values - if more are needed, see:

http://www.briandunning.com/cf/942

---

Actually, after about 100 this would stop working anyway, because of indexing limit.

Edited December 28, 2010 by comment

[David Jondreau]

Substitute ( 10^order - 1 ; "9" ; Char ( 65279 ) )

I was trying to think of a way to get a variable number of characters based on a value without using recursion, and 10^x does it! That's one for the toolbox.

There are ways around indexing limits, but since I don't think you should have a value list with more than a few dozen values, I don't think I care.

[dmontano]

Given: employee reports to another employee.

Table: employee

Relationship: Recursive one-to-many (which gives the appearance of a hierarchy)

I understand the comments about not allowing a parent to be deleted in a hierarchy, however…

Situation:

Sam and Sally report to Bob.

Sam and Sally are "children" of "Bob".

Bob leaves the company.

Bob's record is deleted from the employee table.

Result:

Sam and Sally are without "Parent".

Sam and Sally's record both continue to hold Bob's key in the parent_id field.

Any calculation or conditional formatting that uses the parent_id field to determine or represent the existence or non-existence of a "parent" would give false-positives. If this is not a hierarchy - it sure exhibits some of the characteristics of one: that's why I have used the term. If it is not a hierarchy - so be it. The point is - I am relying on the existence or non-existence of a value in the foreign parent key field to determine if there was a parent or not. This remnant key left over "breaks" that functionality.

Some might say, "Do not delete Bob - make him inactive". That has plenty of merit, but still does not address the fact that Bob would remain in the "chain". In any event, Bob needs to "leave" the reporting hierarchy because Sam and Sally can't report to someone who does not work there any longer.

Another view is that you should not allow Bob to be deleted because Sam and Sally need to report to someone and that Sam and Sally need to be reassigned at the moment of Bob's deletion. That make perfect sense - but I sense that in the real-world, this new reporting relationship for Sam and Sally may take some time to occur. For example, company is waiting to hire a new replacement. Or company flat out does not reassign immediately in cases like this - they just prefer to get Bob out of the database.

The point is - I think that it is quite possible for Sam and Sally to be without a Parent. Yes - the company needs to address who they will report to in the near future - but they can be parentless. Since I have built a mechanism to "show" who is without a parent - it only functions when the parent key is gone. I am sure I am missing something here. Last time I approached this by trying to find all children of the record being deleted and purge the related key to at least for the moment reflect the truth that Sam and Sally are parentless. Previously, I recall this turned into a debate that went south and I am not looking to do that again. I am only observing this and asking here what recommendations other may have.

Thanks again

[comment]

I understand the comments about not allowing a parent to be deleted in a hierarchy, however…

Situation:

Sam and Sally report to Bob.

Sam and Sally are "children" of "Bob".

Bob leaves the company.

Bob's record is deleted from the employee table.

Result:

Sam and Sally are without "Parent".

Sam and Sally's record both continue to hold Bob's key in the parent_id field.

There is a contradiction here: "not allowing a parent to be deleted" means that this cannot be allowed to happen. User attempting to delete Bob should get a message, e.g.:

You cannot delete [bob] because [bob] supervises [2] other employees.

Reassign the [2] employees to another supervisor first.

but I sense that in the real-world, this new reporting relationship for Sam and Sally may take some time to occur.

True, but in the real world Sam and Sally would be told to report to someone else in the meantime. In an extremely unlikely case, they would be told to continue on their own (i.e. no parent).

There is yet another way to look at this - but this would come under a different business model (i.e. not just another data model): build a hierarchy of positions, instead of specific individuals filling a position at some time.

[dmontano]

I can see the value of building a hierarchy from positions - but I know that is problematic as well. Company's have positional titles that do not reflect actual reporting structures.

In one department, reporting structure:

VP > Director > Manager > Supervisor

In another department:

VP > Director > Manager

VP > Director > Supervisor

This all started with my attempt to try and learn how to build a recursive one-to-many structure. Many articles use the reporting relationships modeled with employee as the recursive entity - so that is what I did. I think this is part of the reason I got off track. Employee really is a role that a person can play - not the actual person. I noticed this after I stated a company may want to delete an employee record when a person is no longer employed with the company. In fact, companies typically do not delete employee records, but rather deactivate the employee record.

So, let's say Bob is not deleted in any way (person or employee record), but deactivated as an employee. I still need to do something about Bob's previous children records of Sam and Sally.

The options I can think when Bob is deactivated are:

1. As Comment describes - force User to reassign those children records to new boss; (Must allow bypass as reassignment may be delayed, yet Bob's got to go)

2. Find all children of Bob, delete Bob's old key and his children are now without a parent; (Will be flagged as lacking parent)

3. Find all children of Bob, slide each up to the employee Bob use to report to; (May be incorrectly assigned - also prone to not being corrected until well after the fact.)

It seems in any of the three scenarios - the parent key values of these two child records will be altered. The act of replacing those keys in option #2 with nulls does not seem to cause any harm - whereas leaving them creates false-positives. If I always script the deletion of parent keys when a record is being deleted or deactivated, I do not think I am creating a problem, but rather capturing the fact (for however long) those child records cease to report to Bob. At least it opens up the possibility of all three options as being viable without "breaking" the hierarchy calculations or conditional formatting. Whereas "not" deleting leaves only two valid options.

After thinking this through, I think what Comment is poking around with is the business process determines how the data model is structured. Paint me silly but I think this just keeps getting harder.

[comment]

In one department, reporting structure:

VP > Director > Manager > Supervisor

In another department:

VP > Director > Manager

VP > Director > Supervisor

That's not a problem, since it's not the same VP and not the same Director, etc. Instead of "Bob" or "Jim" we have "VP of Production" or "VP of Marketing". The only difference is that "VP of Production" will never be fired (though the same issue will come up when the company is restructured).

The options I can think when Bob is deactivated are:

1. As Comment describes - force User to reassign those children records to new boss; (Must allow bypass as reassignment may be delayed, yet Bob's got to go)

2. Find all children of Bob, delete Bob's old key and his children are now without a parent; (Will be flagged as lacking parent)

3. Find all children of Bob, slide each up to the employee Bob use to report to; (May be incorrectly assigned - also prone to not being corrected until well after the fact.)

LOL, when I was reading this, I immediately wanted to say that it's not your place to make this decision - but you have beaten me to it:

what Comment is poking around with is the business process determines how the data model is structured.

Except I don't know what you mean by "poking around". I am saying this loud and clear.