Omitting Sub-Related Records

[slbr549]

I need to know how to perform an "if" function within a relationship/portal. For example, File A is related to File B using 2 relationships. One relationship is based on a number, the other relationship is based on a profile. It is possible (and probable) that a record in File A will be related to a record in File B using both the number relationship and the profile relationship.

I need to run 2 different reports - one based on the number and one based on the profile. The problem is, if the record in File A is related through the number relationship, I don't want it to also appear in the Profile relationship. I can't simply omit the record because this same record matches other related profile records.

I hope I'm not making this more difficult than it is, but any advice/help is greatly appreciated.

[LiveOak]

You are in control of relationships. A record in File B can be related to a record in File A via two relationships. What the probability is that this will happen depends upon the design of your relationships and the data in your files (no way for me to tell with the information you provided).

As a general rule, it is easier to print your reports from File B and bring any data needed from File A to the reports via relationships. You didn't describe what exactly you are trying to do, but it sounds like you are having conceptual difficulties about how to do something based upon your comments about relationships. -bd

[slbr549]

Thanks for you reply. I'll try to be more specific.

A customer sends in a card with various profile information and also requests information from various vendors by circling numbers.

I have a file that contains the profile and the various numbers that the customer selects. This is File A. File B is the vendor file that is related to File A by either the number the customer selected, or the profile information.

If the client chose specific numbers, the vendor gets this information as a "specific lead". If the client matches a profile for a vendor, the vendor gets this as "general Information". The customers profile can match several vendors.

The problem is that a customer can be related in both ways to the same vendor. I need to figure out a way for the customer to *not* show up in the profile relationship if it already relates through the number relationship.

I have to do various analysis reports based on specific versus general leads, so it will be important for the customer to only relate once for each vendor.

I can right an *if* statement in other programs to handle this type of situation, but I'm having difficulty getting FM to do this.

I apologize if this is a conceptual problem, but do appreciate any suggestions.

[LiveOak]

The problem is not using an "If" statement as part of a relationship. This can be done by including one in the calculation of the index for the relationship. The problem would become that you can't base the calculation of an index on a related field.

There are several options. One is to base the profile relationship in File B on a field which can be used to "break" the relationship. Run a script in File B to set (or reset) this field to break the second relationship if the vendor has been selected by number.

New Index = NewField & OldIndex

If "NewField" is normally blank, all will proceed as before (depending on how the old index field was set). If "NetField" is set to a "1", the relationship will be broken.

I still think the basic problem is structural. One card can have many vendors, one vendor can have many cards. Sounds like what you really need is a join file between the two databases. Entries in this file would be a card/vendor pair. You could have more than one entry for a card/vendor pair (one specific, one based upon profile). This file would be built using scripts as data is entered (or processed after entry). These join file entries could then be sorted and subsummarized to produce the reports you need. The subsummaries could collapse multiple entries and mark the reports with the correct referral type using a calculation.

-bd

[slbr549]

Eureka, I got it.

Thanks for your input. It was most helpful!!!