Jump to content
Sign in to follow this  
HowardS

Relationships - "OR"????

Recommended Posts

HowardS

I'm a relative newbee to FM7; I used to use and pgm FileMaker on a MacSE (anyone remember those?) in the days of steam-powered pc's.

I'm building a db to help with my importing business and I'm having a heck of a time building a relationship between two tables - I'll try to explain simply.

Table #1 - Company, which has fields including CompanyName and CompanyType, which could be "Supplier" or "Client".

Table #2 - Products, which has fields including ProductPicture, ProductSupplier and ProductClient.

When I enter values into ProductSupplier or ProductClient, I use a drop-down menu based on the CompanyName field.

 

Within the Company table, I have a layout for Products. I've defined a portal which shows the ProductPicture from the related table, Products.

 

I'm hung up on defining the relationship. I can make it work uniquely when I define CompanyName=ProductSupplier and to work when CompanyName=ProductClient, but I can't figure out how to do CompanyName=ProductClient OR ProductSupplier.

 

I want to be able to display all products related to the company, whether the company is a supplier or client.

 

Any ideas, solutions or general silly comments are welcome.

 

Thanksabunch

 

Howard

Share this post


Link to post
Share on other sites
SlimJim

Make a new indexed calculated text field in your products table, ProdFinder,

ProdFinder = ProductSuppler & "¶" & ProductClient

Then set up a relationship between CompanyName & ProdFinder. The related records in products will then be the "OR" you are looking for.

Share this post


Link to post
Share on other sites
Robert Schaub

Does a relationship work with carriage return?

[ QUOTE ]

ProdFinder = ProductSuppler & "¶" & ProductClient

 

[/ QUOTE ]

or even a dash number I.E. 123456-1 or 123456-2?

 

Or do they only see the ABC of

ABC

123

??

 

or in the dash example does it only see the

123456 of the

123456-1 or

123456-2

??

 

Take away the carriage return or the dash and then you are on to something?

Share this post


Link to post
Share on other sites
SlimJim

I did a quick test of this before posting and it works as it is. I think the technical term is multi-key and the key values have to be separated by carriage returns. So a record is related if the match field matches any of the values in the multi-key (an OR relationship)

Share this post


Link to post
Share on other sites
Robert Schaub

Try getting info from 123456-2 if it after 123456-1

 

If work with part number at work and had to remove the dash in order to do lookups to the proper record.

 

Your solution may work ,...but what happens when you have

 

Jones

123

 

Jones

234

 

Jones

345

 

Can you do a lookup and get proper info from

 

Jones

234

 

??

Share this post


Link to post
Share on other sites
SlimJim

This isn't really about finding individual records from which to do lookups. The poster was asking for a portal showing all the records which are related either by CompanyName=ProductSupplier OR CompanyName=ProductClient so this is likely to be a one-many relationship and not suitable for a lookup of data.

 

Presumably to any given company record there will be a number of related product records corresponding to the match of Supplier or Client and in a portal you can place the product pictures from each of these records.

Share this post


Link to post
Share on other sites
HowardS

Thanks, SlimJim - I'll give it a try and get back to you. You're correct; I don't want to do a lookup, but to use a portal.

Share this post


Link to post
Share on other sites
HowardS

It works!! Ahhh...you've saved me much hair-pulling-out.

(at least, what's left of it)

I'm sure I'll be back with other questions. This is a great resource!!

Thanks again....

Howard

Share this post


Link to post
Share on other sites
Robert Schaub

[ QUOTE ]

This isn't really about finding individual records from which to do lookups. The poster was asking for a portal showing all the records which are related either by CompanyName=ProductSupplier OR CompanyName=ProductClient so this is likely to be a one-many relationship and not suitable for a lookup of data.

 

 

[/ QUOTE ]

 

OK, it looks like your suggestion worked for the poster. However,

you will eventually run into troubles. What I was pointing out is

a relationship will see only the first match when it is doing a lookup.

So Yes in this case it will show all records in the portal. But

If the poster or his co-worker tries to draw info to another table using this key they may or may not draw the correct info.

 

I would suggest in your calculation loosing the carriage return.

It may save you some embarrasement in the future when a customer calls and says "I did not order any of this stuff I am being billed for"....Just my thoughts.

Share this post


Link to post
Share on other sites
Ender

A multi-key is a great way to get an OR relationship in FileMaker. Generally the relationship using the multi-key is not used for the primary relationship which the lookups use. Luckily, we can have more than one relationship between the same tables!

 

Chopper, I'm a little suprised that you haven't run into multi-keys before. They are great tools to have on your belt. I use them quite a bit. They're the best technique for generating date ranges in versions prior to FM7 (like with Smart Ranges,) they come in handy for portal filtering (like with type-ahead techniques,) and in some cases can be used instead of an additional join table (you end up with a real many-to-many.)

 

If you'd like to see more, I'm sure I can find examples of any of these handy uses of multi-keys.

Share this post


Link to post
Share on other sites
Robert Schaub

[ QUOTE ]

 

Chopper, I'm a little suprised that you haven't run into multi-keys before. They are great tools to have on your belt. I use them quite a bit. They're the best technique for generating date ranges in versions prior to FM7 (like with Smart Ranges,) they come in handy for portal filtering (like with type-ahead techniques,)............

 

[/ QUOTE ]

 

OK ....Duh I get it know, when you say the type ahead... I was thinking direct hits. I see the point now...Sorry for my thick headedness....however I stick to the

dash number therory on the the same line.

 

I pre-built certifications. I have one blueprint that calls out 56

direct dash numbers

 

If I have

nnnnnn-1 to nnnnnn-56 each of which has a slighty differnt pre-built cert. If I try to do a lookup on nnnnnn-32 I get the cert for nnnnnn-1

So I made I calc firstword & Secondword loosing the dash and now everything works cool.

Share this post


Link to post
Share on other sites
HowardS

Ender & chopper - I'm trying to follow your thinking to see if I might run into problems. What impact does the carriage return have on the formula? Will it still work if I lose it?

...just trying to learn more...

 

Howard

Share this post


Link to post
Share on other sites
Ender

Hi Howard,

 

SlimJim gave you a good solution. The carriage return is necessary to make your multi-key, so try not to lose it. wink.gif

Share this post


Link to post
Share on other sites
Ender

Chopper, I'm not sure what the problem is with your relationships, but when I try your dashed IDs, the lookups work fine.

Share this post


Link to post
Share on other sites
HowardS

Thanks, Ender.

Good screen name and nice graphic, by the way.

Share this post


Link to post
Share on other sites
Chriz

It seems that SlimJim Ender & Chopper have nutted a great deal out so maybe this might be the right thread to ask a similar question.

 

It is to do with network marketing and I am trying to find a way to generate a report of a hierarchy or genealogy, so I have an ID# being a Sponsor ID, the Sponsor ID is linked to any number of Networker ID's (one to many relationship). What I wish to do is generate a report that shows all of the Networker ID's and names associated with the originating Sponsor ID, however I would also like to be able to capture the ID# and names of those associated with any of the Networker ID's that are linked to the originating Sponsor ID. Effectively there are any number of Sponsor ID's generating more Networkers and more Sponsors. I am trying to capture what is termed the "downline" or the Family Tree in genealogy terms, so capturing the parent so to speak and the children or grand parents, aunts and uncles, kids and cousins.

 

So now that everyone is confused would a similar 'multi-key' work here and I have to say that I have no real idea of what they are and what to do with them but the discussion looked promising within my framework.

 

Thanks in advance for any input.

Share this post


Link to post
Share on other sites
SlimJim

This looks to me like a problem of a different order of complexity, particularly if you start with a sponsor who has a sponsor. Indulge me in a theoretical discussion. Your statement that the Sponsor to Networker relation is one-to-many is the key.

 

Separate out all the Sponsors who have no sponsors. These are the starting points of the tree structure - the roots in mathematical terminology. If you look at the tree generated by one of these then the one-to-many means that it is disjoint from the tree generated by any other root. So in terms of your reports you are generating a report for each root so in a sense you only have to solve the problem with one root and then apply the solution to the many-rooted problem. Now a computing model that we all know. Think of a root sponsor as the root of a hard disk, folders are the networkers, a folder with subfolders is a sponsor etc. I think this is the level of complexity of your system and suggests that perhaps you should apply that model and label your networkers in the same way as you label the folders on your hard disc, by chains. Initialising this is a one-time operation. Find all the roots and label them, find their networkers and label them etc. Once all your existing networkers/sponsors are labelled then labelling new ones is simple, like labelling a new subfolder on a hard disk. Having got a label for each person you can set about constructing the relationship. Call the label just constructed the TreeID. It is essential that this is a single value (i.e. contains no carriage returns) From this TreeKey we need to construct a BranchMultiKey, again this is best done recursively one off and then updated as you add new records. So the BranchMultiKey of a new networker is equal to the BranchMultiKey of the sponsor with an added Carriage return and the TreeID of the networker.

 

For example, using the folder analogy(ignore the : after the hard disk name)

C\Windows\System32 would have a multikey of

C

C\Windows

C\Windows\System32

If you now make a self-relationship of TreeID match BranchMultiKey then in one direction you pick up all the networkers and their networkers and in the other direction the sponsors, their sponsors etc.

 

Word of Caution - I have not tried this out. As I said a theoretical discussion.

Share this post


Link to post
Share on other sites
Chriz

SlimJim, such a way with words.

Ender, I saw that thread some time back and downloaded something from it but it did not do what I needed.

 

As I am in the designing stage, anything is possible. My process or system works like this at present. There will be an originating Sponsor and everything branches out from there, however it is not my desire to report on every member under that sponsor but to capture say 10 levels.

 

The directory ~ subdirectory ~ subsubdirectory principal is fine, an employee tree is fine and I downloaded a hierarchy example from FMForums that looks interesting but I still have to get it clear in my head what process to use.

 

So a Networker gets busy and works their business and then becomes a Sponsor and the Networkers under that person also get busy and multiply and so the Networker becoming a Sponsor goes on and on. When a Networker sponsors someone, that individuals 'NETWORKER ID' becomes the 'SPONSOR ID' for the sponsored person. Essentially on the new Networkers file the ID# is placed in the field "Sponsor ID#", so every new Networker has their own ID# and the Sponsors ID# on their file.

 

So what I would like to do is call up Sponsor ID A14398C and request a report for all individuals that are either directly linked to that ID# or associated to it through a hierarchy. So we have one ID that may have only 10 directly linked to it at level one below from there on the next level Sponsor ID is the identifiable one. This is the HD or directory subdirectory scenario and if it can be captured and halted at say 10 levels deep this would be fine. This is bearing in mind that at the HD it will immediately divide into 10 directories, with which each directory may in turn divide into 10 each subdirectories, dividing by 10 again and again and so on. It gets a little awesome as by level 5 we are looking at capturing 1,000,000 plus ID's and names or identifying 1,000,000 subdirectories spread out like tree roots. I also look at it as identifying a computer, then its major hardware components, then the components that make up the major hardware component getting right down to the IC's etc, each one individual but each linked to the component above.

 

I am very much a beginner here, great concepts, can create good looking layouts and get information to be passed from one Db to another related Db by inserting an ID and many other things like that but scripting is something that I have managed only by copy and past and tweaking in the most basic manner to get surprising and pleasing things to happen, to me anyway. So hopefully I am not asking for to much on this quest.

Share this post


Link to post
Share on other sites
SlimJim

I think I understand the structure generally to generate the Tree structure you need a separate set of ID's which define the tree and a Level number if you intend to restrict the depth of search. Construct the TreeID as I indicated in the previous post.

 

Set TreeID of the root as 0

Set TreeID of a networker = TreeID of sponsor & "." & SerialNumber

Assign the serial numbers starting at 0 to the netwrokers of a given sponsor. The level of a networker is the number of full stops in his tree ID maybe best stored in a separate field.

 

To find all the networkers and their networkers etc starting at a given sponsor do a find on TreeID by putting in the sponsors TreeID and then restrict the level by an inequality on the level. This will produce a found set consisting of all the networkers of the sponsor, all their networkers etc.

 

Now the fun begins - what kind of a report are you going to produce from this set? Enjoy and keep us informed.

Share this post


Link to post
Share on other sites
Chriz

Well SlimJim, this potentially looks good "Set TreeID of the root as 0

Set TreeID of a networker = TreeID of sponsor & "." & SerialNumber

Assign the serial numbers starting at 0 to the netwrokers of a given sponsor. The level of a networker is the number of full stops in his tree ID maybe best stored in a separate field.

 

To find all the networkers and their networkers etc starting at a given sponsor do a find on TreeID by putting in the sponsors TreeID and then restrict the level by an inequality on the level. This will produce a found set consisting of all the networkers of the sponsor, all their networkers etc." However I wish I knew what some of it meant.

 

I basic terms are you suggesting that I would require to assign 2 sets of ID's to each individual (Networkers maturing into Sponsors) in order to capture the information, one being their registration # (Networker # maturing to a Sponsor #, still one in the same in real terms) and another ID# for the purpose of the formula, or am I confused right from the start. The "full stops" have me beat already, although if you are suggesting that if I want to go down 10 levels in the tree I would put 10 full stops in the formula then I can cope with that ~ I think.

 

At present this project is all theory, like a thesis except when I can sell my house it will all be put into practice, so to test this out I would have to create a number of imaginary sponsors and networkers. As for the kind of report to be generated, it would I suspect have to be in simple running text format as any structured layout other than keeping levels separated by a blank line could be overwhelming and use a lot of paper to print up. A comma delimited text format may be the best as having an ID # and a name would be all that is required, or a contact phone maybe of use.

 

In setting up the DB I created all sorts of Tables to give the option of various Finds or Look Ups and even created a number of Portal combinations to see what would happen. Somethings worked, most haven't and I have only just begun to take a fresh look at it again after 4 months of hands off. One aspect was getting a formula I think, another was to get a way of displaying it, this being the Portal, then how to print it. Me an Portals haven't got on well to date.

Share this post


Link to post
Share on other sites
SlimJim

You would need two IDs. The TreeID is purely there to enable you to reconstruct the Tree structure.

 

While re-reading this post although this is a standard mathematical Tree structure I got the vague recollection of having seen this before in a Filemaker Context so I have had a search around and re-discovered this paper on Hierarchical Portals by Excelsis (free download and well worth a look before you commit your design). It is on the following page:

 

http://www.excelisys.com/services/fmp7/tips_tricks.htm

 

I think it explains the possibilities in Filemaker rather better than I can.

Share this post


Link to post
Share on other sites
Chriz

I downloaded the Excelsis Hierarchal Portal last week and stair at it with interest, this brain dead feeling comes over me when I read the pdf. I bummed out at math some 35 years ago and the brain does not have a quick fire controller input device that is easily accessible.

 

My records begin with having a Contact that has a unique ID, this is just the normal auto generated contact management ID# consisting of numerals only, the contact is also given a unique Network ID# which consists of 7 alpha numeric digits a letter followed by 5 numbers and then a further letter, this gives me a huge range of digital ID's for the future. This number is always associated in the Sponsor scenario, my thought is that the Contact ID# could be used as the TreeID. ??

 

The Excelsis Hierarchal Portal would be great if it accepts the alpha numeric numbering, I would love to be able to try and create a relationship between my Networkers Contact Db and the Hierarchal Portal but I think that I am biting off more than I can chew here.

Share this post


Link to post
Share on other sites
SlimJim

I have just skimmed through the excelsis pdf again and on the basis of what you have said here I would say that this is exactly what you are looking for. I would strongly recommend that you look at the sample file and despite the problem look at the pdf, it is not necessary to understand the whole thing you just need enough to reconstruct the structure in your context Sponsor(Parent) and Networker(Child)

Share this post


Link to post
Share on other sites
Chriz

I read the pdf through and it looked very interesting and maybe one day it will make sense as I tend to have a quick look at the index then try it and if all else fails read the instructions. I might email the designer to shed some more light on its potential as I wonder about printing out the results as well.

 

Thanks for your input SlimJim, timing was hopefully good on this one and time will tell of the results. My project is going to be in India, hence the need for capturing potentially a very large range of numbers.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use