Portal Filtering

[hall312]

Just not quite grasping Portal Filtering and Global Storage.

Database consists of investigations records. Each record has a case number. Each record has, for ease of explanation, a to do list portal. When a to do item is completed, the completion date is entered in the portal. If it is not completed the date field is empty.

I need a separate layout with portal that displays all the case numbers with non completed to do items. Basically show me which date fields are empty.

I've checked all the posts for days and I just cant grasp it. I can pick apart an example db pretty well if someone could throw together a very basic one. Thanks.

[Ed Bradley]

Just double click on the portal and choose to sort by date and the empty date fields will appear first.

[hall312]

Ultimately that would be the easiest solution, but after 2-3 thousand records it would get a tad bulky. Global field will easily fix my simple problem, just need a little guidance. Thanks anyway for the reply.

[Ed Bradley]

Sounds like Global it is.

Edited November 11, 2009 by Guest

[LaRetta]

I would do it this way: Add a calculation in the ToDo table called Status (active or done). Create a custom value list with active and done in it. Put global status in Investigations and include that field in your relationship.

See demo file. There are other ways but this way will allow you flexibility to see one, the other or all by selecting from the checkbox and you can add other types of status by simply adding to the custom value list and changing your ToDo status calculation.

Investigations.zip

[hall312]

Spot on, LaRetta! Thank you.

[hall312]

I've attached a very simple file. On the layout with only a portal, I've managed to get it to show all my records on one portal. Now I need it to filter between active and done tasks. LaRetta helped me earlier but I'm still not getting it (after hours of trying). I know its a global field thing. The ultimate goal is for this layout to be the start layout and by a simple glance I know which cases need action. Nothing will be entered on this layout, just provides an overview of ALL cases. This will complete my database so help is greatly appreciated.

Test.zip

[TheTominator]

Change the relationship between NextActionPortal and NextActionForm to be only Status=Status.

The X relationship you have there is good for accessing global values in other tables, but that isn't what you are trying to achieve here.

[Lee Smith]

I have merged your to Topics. Please do not double post your questions. It is not necessary to start a New Topic when asking for additional information on the same Problem.

Lee

[hall312]

Thanks for everyone's input so far...works great in my main db.

I now have another portal that I want to show all tasks that are past due and active. I have the relationship set up as in the attached file, but I am running into the issue of having an unstored field on the child side. Does what I want except I can't delete any records if need be. Any help on the solution would be great.

Test.zip

[LaRetta]

Go into your relationship graph and select the x between Next Action Portal and Next Action Form. You cannot have 'delete related records in this table' checked on for Next Action Portal because you have unstored values on that side.

I don't understand why you have two tables here anyway. You can put your two globals (global status and global due date) in your Case Number table and attach ANOTHER table occurrence of your Next Action Form directly to Case Number using:

CaseNumber::CaseNumberPKID = NextActionForm::CaseNumberFKID

AND

CaseNumber::globalStatus = NextActionForm::Status

AND

CaseNumber::globalDueDate = NextActionForm::DueDate

:smile2:

[hall312]

Tried the changes you suggested [color:red](attached file with changes) and am not getting the result I am looking for. I ultimately want a layout with just a portal on it showing the overdue next actions that are still active. I am understanding the movement of the global fields to the case number table. Now how would the layout be created - would the layout setup get records come from casenumber, nextaction, or nextaction2? Thanks for your patience, I'm getting there! :crazy2:

Test.zip

[LaRetta]

Very close. I told you wrong about using global date. Since you want all past due, you want older than the current date but still marked Active. So change the relationship from CaseNumber to NextActionForm2 to be:

CaseNumber::CaseNumberPKID = NextActionForm2::CaseNumberFKID

AND

CaseNumber::gStatus = NextActionForm2::Status

AND

CaseNumber::cToday > NextActionForm2::DueDate

And be sure that cToday calculation in CaseNumber - which is Get ( CurrentDate) - is unstored and NOT a global calculation. Also, it is recommended that you preface fields with the data type if global or calculation so you know that they are 'special' fields. I start with 'c' if a calculation and a 'g' if a global field. With those changes, I think you'll be good to go - let us know if not. :wink2:

[hall312]

Now we're getting closer. How do I get all the case numbers with past due active tasks on to one portal? If I take away the relationship

CaseNumber::CaseNumberPKID = NextActionForm2:: CaseNumberFKID

it shows what I need on one portal but repeats the portal 884 times (number of case number records). Is there a way to keep that portal at one record?

Thanks for the field preface tip - I always wondered what those were in other peoples examples.

I'll owe you $$ if I keep picking your brain like this.

Edited November 16, 2009 by Guest

[David McQueen]

Just not quite grasping Portal Filtering and Global Storage.

Database consists of investigations records. Each record has a case number. Each record has, for ease of explanation, a to do list portal. When a to do item is completed, the completion date is entered in the portal. If it is not completed the date field is empty.

I need a separate layout with portal that displays all the case numbers with non completed to do items. Basically show me which date fields are empty.

I've checked all the posts for days and I just cant grasp it. I can pick apart an example db pretty well if someone could throw together a very basic one. Thanks.

You would pretty much like this to be automatic.

In the "Case Table" create a calculated field of type text:

ToDoDoneKey= CaseID(as text) & "space" & "1"

In your ToDoDone table create two calculated keys, one for ToDo and one for Done:

ToDo: Case (IsEmpty DoneDate;"1";"0")

Done: Case (IsEmpty DoneDate; "0";"1")

Then compound each of these keys with the CaseID in the ToDoDone table:

CompoundToDo: CaseID ( as Text) & "space"& ToDo

CompoundDone: CaseID & "space" & Done

Everything in your ToDoDone table must be indexed storage.

You should now be able to create two relationships from your Case table to your ToDoDone table:

To Do: ToDoDoneKey::CompoundToDo

Done: ToDoDoneKey::CompoundDone

And show a portal for each relationship.

If you add a date into the Date field in the To Do portal and then click out on the background, that line should disappear from that portal and show up in the done portal.

Conversely, if you remove a date from the date field in the Done portal and then click out against the background, the record should disappear from the Done portal and reappear in the To Do portal.

HTH

[LaRetta]

How do I get all the case numbers with past due active tasks on to one portal? If I take away the relationship it shows what I need on one portal but repeats the portal 884 times (number of case number records). Is there a way to keep that portal at one record?

Then it sounds like it shouldn't include Case Number then (as you've figured out) and shouldn't be displayed on Case Number layout either or it WILL display on every Case Number. It sounds more like a dashboard or main menu display. Do you have a one-record preference table or globals table? It doesn't matter where you put those globals and attach the relationship just be sure and change the layout to be based upon the new 'parent' relationship (Preferences) and keep the NextActionForm records as the child side so you don't run into issues where you were attempting to make the global and unstored relationship as the child.

See attached (and notice that I now ditched the second table occurrence of NextActionForm because I could use the first one. :smile2:

TestREV.zip

[LaRetta]

Hi David,

I haven't had the need to create all of these types of extra concatenated keys since vs. 7 came out. Since we can join to multiline global status AND dates (and many other combinations) in multi-predicate style see here there simply isn't the need.

[David McQueen]

Hi David,

I haven't had the need to create all of these types of extra concatenated keys since vs. 7 came out. Since we can join to multiline global status AND dates (and many other combinations) in multi-predicate style see here there simply isn't the need.

Hi LaRetta

1. Old habits die hard :-)

2. It is just my impression, no serious study here, that a simple relationship is faster.

3. I find debugging simpler if all of the logic resides in one final field. I do not have to troll multiple relationships looking for the problem.

4. In the case of a concatenated key ( CaseID& " "&"1") it is pretty much optional which way you use.

If you end up using compound keys,ie: keys in the form of a return delimited list, or compound concatenated keys, ie: a series of concatenated keys in a return delimited list, it may or not be possible to represent it in a multipredicate relationship, but depending on what you are doing, it may be much much easier to see what is going on just looking at the key structure.

So it is not a point that I do not realize that there are multi predicate keys, but rather sometimes, I do not want to use them.

In this case it is totally optional. For someone coming from other databases where multipredicate keys are the norm, it would probably also be easier for them to understand, staying away from things particularly "FileMakerese".

This person was looking for a method to abtain a particular goal. This does it and does it without setting globals as you go along. As you enter and remove dates, it just happens. If he understands it easier with a Multipredicate relation and still obtains his goal, all is good :-)

[Søren Dyhr]

It is just my impression, no serious study here, that a simple relationship is faster.

Isn't it a matter of when and where, commiting a record with a comprensive onfield keystructure puts strain on the indexing, the linking is however based on 3 shorter indexes than one single and long if split into 3 keyfields... well it's a tough one to prove!

But if we turn to something like recuring events in calendaring solutions are there only some of the keying which could be broken out in several keys such as DayName or number, but the problem arises when attempting to make a set of keyfields to optain say each 10th day disregarding which month an appointment should encounter.

Here are repeating fields even more up to it than pilcrow delimited, unless of course they're made out of recursive custom functions.

Custom functions are safeguarded against endless loops when turning recursive and terminates when reaching a specific number of turns ... here is there a price to pay for the distrust to the sloppy developer, they're slow. This have made developers prevent recursions in the CF's by using Evaluate( to statements build by substitutions ... I'm however not convinced they're fully justified in all circumstances.

But I have found that I can make pretty agile solutions by not subscriping to one specific method, but instead mix both (3 ways actually) ways if needed.

On the other hand have it been hunched that filtering via multihop relations to optain the filtering could put a strain on the "engine" if the nesting gets too deep and the bidirectionality avoided via Achor Bouy is taken too deliberate.

--sd

[hall312]

LaRetta - That is perfect! Thank you so much for your help. I owe you one.

David - Thanks for adding another "tool" to my arsenal.

Soren - all I can say is wow - I can't even begin to add to that - I'll stick to catching bad guys on the street and leave the database "talk" to you and others much smarter than I. Thanks again.