Field as Pop-up Value List with Repetition

[mcschan]

Dear FM Experts,

I am building an Order Incidents database for my company, which tracks wrong orders that were placed.

I have 2 tables: Order Incidents table and Root Cause Types table. Order Incidents tracks the actual wrong orders placed wherewas Root Cause Types tracks the cause of each wrong order.

I have a field called Order Incidents::Root Cause Code which is related to Root Cause Types::Code and Root Cause Types::Name which is the detail name of the corresponding Root Cause Code.

In my layout for the incidents I have the field Order Incidents::Root Cause Code and Root Cause Types::Name defined with 5 repetitions. Also, I set up the Order Incidents::Root Cause Code field as a pop-up list, taking values from the RootCause value list, which is built upon the Root Cause table. This is to allow users to choose the root cause code (and FileMaker will automatically lookup the name of the root cause)so that we have a finite set of root causes.

My problem: the lookup for root cause name only works for the first repetition. The user would be able to choose additional root causes but the name of the root cause code will not be looked up. How can I separate the repetition so that they do their own look up.

Also I am trying to build queries that looks up for cases where the root cause is, for example, "not enough information" or "not enough time", either as the primary cause (first repetition) or any of the secondary causes (the second to the fifth repetition). For now I can only query the primary root cause. How can I build the query so that it looks at all the repetitions?

Thank you very much

Matthew Chan

[Søren Dyhr]

My problem: the lookup for root cause name only works for the first repetition. The user would be able to choose additional root causes but the name of the root cause code will not be looked up. How can I separate the repetition so that they do their own look up.

By making a relation and a portal ...the lookup is storing redundant data - because by entering the ID in a portal the value is tunneled into the portalview, and no data is copied at all.

This is a FAQ that might be the first pithole you expirience to fall into ...never the less is it a conceptually assumtion that almost everyone makes, initially!

--sd

[mcschan]

I made a minor mistake on my previous post. The secondary repeating Order Incidents::Root Cause Code fields do look up for values in Root Cause Types::Name after I chose them from the value list. However, the values for Root Cause Types::Name will only be entered in the first repetition. In other words, the second Root Cause Types::Name will replace the first Root Cause Types::Name, the third will replace the first, the fourth will replace the first and the same is true for the fifth. How do I make the repeating field so that the second root cause Name will get enter into the second repetition, the third root cause Name will get enter into the third repetition, etc etc?

[Søren Dyhr]

You're causing youself a lot of worries in the future if you continue to assume repeaters will be of much (if any at all) use in solid founded database models:

http://databases.about.com/od/specificproducts/a/normalization.htm

--sd

Edited August 17, 2005 by Guest

[mcschan]

Hi Soren Dhyr,

By look up I don't mean the act of storing redundant data. All the root cause code and root cause names are stored in the Root Cause Types table. I have the relationship Order Incident::Root Cause Code = Root Cause Types::Code, and this relationship is not looked up. However, when I choose the root cause code from the value list it automatically fills in the root cause names. It is this automatic filling of the name that's causing problems for me.

Thanks Matthew

[Søren Dyhr]

But repeating fields breaks 1NF ...but why don't you attach your file or a template using same reasoning. Then would I have a chance to look at it, and most likely be abel to make what you're after with a portal instead!!

--sd

[mcschan]

Hi Soren Dyhr,

I should have attached it earlier but I had to get my manager's approval. I've attached it to this post. Just in case it asks for a user account, it's Admin with no password.

After you opened the file, turn to the Enter Incidents layout. You'll see in the middle of the layout there are the Incident Root Cause fields. If you just click on the first field it'll show a value list whereby you can choose the predefined values. Go down a row and choose a second root cause, you'll find that the Name of the Root Cause gets placed in the first Name field. I want the second Root Cause name to be associated with the second Root Cause Code.

Also if you go to the Query layout and select a Root Cause to query, it'll only look for the first Root Cause, instead of looking at all 5. Is there a way to make this query so that FileMaker looks at all repetitions?

I'll read the web site you attached to see if I can make any sense out of it.

Thanks

Matthew Chan

Order_Exception_Analysis.zip

[mcschan]

Could anyone please help me here!

[mcschan]

BTW, this database that I am writing will be web published. I have value lists that displays values from two fields. However, when web published, only the primary field values are displayed, not the secondary field. How do you fix that?

[Søren Dyhr]

I should have attached it earlier but I had to get my manager's approval. I've attached it to this post. Just in case it asks for a user account, it's Admin with no password.

Yes. Take a look at the alterations I made - and sorry for keeping you in suspence. Fenton an I mentioned in another thread how much havoc it causes that the templates following the application still contains repeaters ...which very much is to our dissapointment. We suggest that allthough the ensurance of flat learning curve is the aim with them ...are they basicly wrong - should therefore be removed from the templates at least. When portals and relations are around.

--sd

Order_Exception__sd.zip

[mcschan]

Hi Soren Dyhr,

Thank you very much for your help. Would you please explain to me how and why this works? Why does a portal work in this case? Thanks.

Also, because I've made a number changes to my db since I sent you the file, I basically need to migrate what you had to mine. To get 5 rows in the portal, do I have to create 5 identical records in the APortal table linking to the same invoice number? How do I get rid of the borders of the portal?

Matthew

[Søren Dyhr]

Why does a portal work in this case? Thanks.

Because only lookups copies dependant on the repetition number ...but it also means redundant storing.

How do I get rid of the borders of the portal?

You're right they are not as forgiving as repeaters where you could make a massiv block, even when trying to make pen invisible will the block break slightly.

do I have to create 5 identical records in the APortal

No, use instead allow creation of related records in the relations def.

--sd

[mcschan]

Hi Soren Dyhr,

Thanks for all the great help. I have just one final request. If you go to HomePage > Select Query to View Counts > choose a Root Cause > press View Query; this will bring you to a portal with all the incidents that had the root cause chosen. Before I switched over to the portal method of storing root causes I had this function working. However, because the link OrderIncidents::k_incidentRootCause = Root Cause Types::kp_rootCauseCode is broken, this won't work anymore. I have instead the relationship OrderIncidents::kp_orderExceptionIndex = Root Cause Portal (same as APortal):Order Exception Index and Root Cause Portal::Root Cause Code = Root Cause Types::kp_RootCauseCode (see attached picture). Would you please help me with this query? How should I relate Relationship_Root Cause so that it actually looks at the root causes in each incidence? Thank you very much. You are the man!

Matthew Chan

[Søren Dyhr]

I think I know the answer to your question, although you manage to hide it well in you post - the question you have is a tunneling question, which means that a value should be seen thru several relations. Which is possible by making another path with TO's from the same tables involved.

Either you investigate this:

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

Or you send me a clone of your file, to me privately ...you'll find my mail adress in my profile.

--sd

[mcschan]

Alright I'll send my db to your email [email protected]. It has the title "mcschan's database for tunnelling relationship, from FMForums.com"

Thank you for all your help.

Matthew Chan