Relational IF help

[drbott]

I'm wondering if anyone can offer me some help. I'm trying to do what I think should be a simple thing, but I can't seem to figure it out.

What I want to do is a simple check to see if there is a certain related item in the database. Our situation is that we want to check to verify that a catalog has been added to certain orders.

I'm currently going to the other table and looping through the items to see if it is there, but I feel like I should be able to do it with a calculation from the main record.

I want to be able to do something like the following but that only checks the first item in the relationship.

PatternCount ( Order Items::Name ; "Catalog" )

Am I making sense? Any suggestions?

[T-Square]

If you use the right relationship, then your calculation will work--if there are no related records, then PatternCount will be False, which you can easily test for.

What I suspect is going on is that you are not using the "right" relationship. Specifically, you will want to build a relationship that links fields in each table that have the value you are trying to test on. For example, if you are trying to test whether this Order has a "catalog" entry in the OrderItems table, then you need a relationship that is built using both the OrderID (presumably) AND the ItemType field. Once you build this relationship (probably using a global field in the Orders table), you can simplify the test. I think if you just check for the presence of the OrderItem ID (whatever you've called it), you can tell whether there's a record on the other side of the join.

Have I got it right?

David

Edited January 21, 2006 by Guest

[Breezer]

Have you considered using a portal to do this?

If I understand you correctly, then, this is how I would approach this issue.

Create a relationship using a global field such as g_Catalog and relate it to Name. Create a portal using this relationship. When you type "Catalog" in the g_Catalog field, the portal will be populated with all the orders that have "Catalog" in the related Name field. From here you can either scroll thru the portal to see if any of your orders are missing or you can set up a "Multi-Key" field that will compare the orders you type in the "Multi-key" field with the orders in the portal.

[drbott]

So I'm guessing that there isn't a way to evaluate all the related records at once through a simple calculation.

My whole point is that I'm trying to avoid unnecessary interface elements or script steps to perform a simple check. That would eliminate any trickery using portals, etc.

So far, the best solution I've found is using the new GetNthRecord function and doing a single calculation that will grab a list of the items in the order looking for my text (Catalog in this case.)

The negative of course is that I have to do an item for each potential related item.

PatternCount(

GetNthRecord ( ItemName ; 1 ) &

GetNthRecord ( ItemName ; 2 ) &

GetNthRecord ( ItemName ; 3 ) &

GetNthRecord ( ItemName ; 4 ) &

GetNthRecord ( ItemName ; 5 ) &

GetNthRecord ( ItemName ; 6 )

;"Catalog")

Seems like there should be a way to check all the related items at once. No?

[comment]

Of course there's a way:

Define a value list using values from field Order Items::Name, using only related values starting from Order.

In Orders, define an unstored calculation field, using Position() or PatternCount() to check if "Catalog" is included in ValueListItems().

[Søren Dyhr]

Since drbott is on fm8adv could he instead of the ValueListItems( function, as Michael suggests ....use a modified version the CF used in this template:

http://www.nightwing.com.au/FileMaker/demos8/demo804.html

...which respects found sets made in other ways than a GTRR(SO) bearing in mind that GetNth( is meant to be utilized just as much as ValuelistItems( according to Shaun Flisakowski from Filemaker Inc. - If you can see the point in contraining more customized than multicriteria relations dictates might it be the choise? I'm here talking of situations where you are forced to index a calc'field derived on values in the record, to make the multi criteria work, where indexing the original field in question might be more flexible.

So this might not be paticular daft:

PatternCount(

GetNthRecord ( ItemName ; 1 ) &

GetNthRecord ( ItemName ; 2 ) &

GetNthRecord ( ItemName ; 3 ) &

GetNthRecord ( ItemName ; 4 ) &

GetNthRecord ( ItemName ; 5 ) &

GetNthRecord ( ItemName ; 6 )

;"Catalog")

....If we take the recursivity of CF's into equation, like Rays template shows.

--sd

[drbott]

Thank you all for your help. Looks like the ValueListItems() solution is the closest to what I want. Am I the only one who feels it is a little crazy to have to use outside things like Value Lists or Portals just to get the contents of related fields?

Our solution is one that has been developed constantly over the last 7 years and I'm working hard to keep everything as straight forward as possible. I'm always afraid I will do something minor that will break a script somewhere. Because of that, our new credo is to try to have everything contained in single locations with the fewest dependancies as possible.

I think this situation, what I'll actually do is do with a few extra script steps and transfer the related data into a variable. This will allow me to loop through the items putting the data into a variable. Something like the following:

Set Variable $Count to 1

Loop

Set Variable $Names to GetNthRecord (Name ; $Count) & $Names

Set Variable $Count to $Count +1

Exit Loop If $Count > Count(Names)

End Loop

PatternCount ($Names; "Catalog")

This will keep everything within the single script and keep me from creating dependancies on external Value Lists, calculations or portals.

Thank you all for your help!

[comment]

I don't know why a value list or a portal would be an "outside thing" - not to mention a calculation. These things are an integral part of Filemaker and they are there to be used.

[drbott]

Perhaps this is a philosophical issue more than anything.

Just to give you an idea of our solution. We've got a large solution that we run all of our company infrastructure on including shipping, receiving, orders, inventory, etc. We're talking gigs of data, thousands of records being created each day and hundreds of scripts. We've been refining it for about 6 years now adding new features and updating with the latest technology from FM.

Our principles of development is to try to keep all the logic contained in a single location. Avoid using user interface elements as programatic tools. Avoid creating fields that aren't for storing data whenever possible.

These principles are both to maximize performance and to help with the maintainability of our system. When you have a specific operation that uses fields, scripts, layouts, portals and value lists there are many points where a single change can cause that operation to malfunction. If it is all contained within a single script, you have everything you need right in front of you if you come back to it years later.

Not everyone's solutions will require this level of vigilance but for us, anything less would let things get pretty crazy.

[Søren Dyhr]

I don't know why a value list or a portal would be an "outside thing" - not to mention a calculation. These things are an integral part of Filemaker and they are there to be used.

It's philosofical and I'm with Michael on this view! Eclectics will deliberately ignore the gist of a fair share of tools they encounter and use. Steering projects more on hearsays and vanity - is a desire to execute political power to avoid it came to public knowledge that the lack of skills is the real problem.

http://www2.pfeiffer.edu/~lridener/courses/COLLINR2.HTML

I've seen such demands before, or rather the objectives for making SFI systems also called "separation model" solutions is sheer anxiety for the downtime caused by new implementations. In such solutions can a buggy interface solution easily be swapped with the older if something is wrong in a few seconds.

This often occurs in a lighter form, where calcfields generally are avoided and the whole business logic lives in the scripting ...this is where you might be better off with another tool.

Why? Well it's an entirely different approach from the tool for "workgroups" as Dominic Goupil hyped some years ago as key-marked, to grievance to developers more by name than by qualifications who more or less have dreamed of a concessioned marked, where too much of the previous encountered merithocracy "ought" to be tamed by virtual turnpikes

What you seems to be candidate for is to give your solution a whirl with this tool http://www.fmpromigrator.com/ away from the too eagerly knowledge sharing approaches Filemaker deliberatly endorse.

You organization is seems larger than the usual size the tool is targeted towards, on the edge of stepping into SQL solutions. Willing to pay for a dedicated IT dept. staffed with people carrying master degrees in computer science.

--sd

[T-Square]

I don't know about the whole Value List or portal thing, but, just to return to my original point:

You wanted to check whether there was an intersection between Set A (your current order), with Set B (Order Items), where Set B::Item Name = "Catalog"

If you create a global field in Set A with the value you seek (AGlobalField = "Catalog"), then this can be handled via a relationship:

Set A::ID == Set B::SetAID

AND

Set A::AGlobalField == Set B::Item Name

This set will be empty if there are no records in Set B with the selected Item Name. There are no loops, and the test is simply whether there is a record on the other side.

David

[comment]

Yes, of course. But it seems the problem is the item is not always named "Catalog"; it only CONTAINS the word "catalog".