Jump to content
Sign in to follow this  
Prith

maximum number of self joins??

Recommended Posts

Prith

Hi,

 

Well, I have this Evaluation table with atleast 15 fields - Event Code,Department, Job Title, etc. I need to calculate number of records from a particular department, number of records from people with a particular Job title etc. So I made a lot of self joins with different fields. I also need Count of records with a specific JobTitle from a specific department, say records of Professors from chemistry department. So I created a text field JobDept, which is Auto-Enter with Calculation - "JobTitle & Department" and made a self join relationship using this field.

 

I created another field which is a number with auto-Enter with Calculated value - Count(RelationshipName::JobDept). I did this for all the combination of fields of which I needed a count. So I did 14 self Joins!! All the "Counts" work except for one. I checked a number of times and I haven't made a mistake in making the relationship or anything.

 

So is there like a maximum number of self joins one can make to a table? I actually redid the whole thing and I get the same error. One Count field is never populated. It remains empty. I tried changing it from a number field to Calculation field and it doesn't help. I am not sure what the difference is anyway...

 

Does anybody have any ideas?

 

Thanks.

Prithvi

Share this post


Link to post
Share on other sites
Ugo DI LUCA

Hi,

 

There's no limitation in SelfJoins as far as I know, even if having so many relationships "active" can slow things down.

 

I would rather suspect an indexing problem, here with either a key not indexed, or exceeding FileMaker indexing Length limits, or a mismatched relationships on field types.

 

What exactly is this relationship ?

Left Key structure, format, type of result ?

Did you make sure each key on both sides are equal ?

 

Give us a concrete example with the key (it's calculated result) you have for the Left side, and what you have on the Right.

 

Cheers

Share this post


Link to post
Share on other sites
Prith

The result of the calculation is a number and is stored in a number field. I don't quite know when to index a field and when not to. So that, as you suggest might be the problem. I have tried to explain my problem below again.

 

I have the Evaluation table with fields EventCode, Recommendation etc. Both are text fields and are indexed. I created a text field Eventcode_Reco, Auto-Enter with Calculation field "EventCode & Recommendation". this is indexed too. I did a self join using Eventcode_Reco.

 

I created another number field, Count_EventReco(left key), auto-enter, Calculation being Count(Evencode_Reco_relationship::Eventcode_Reco)- right key. This field is not indexed.

 

Eventcode_Reco gets entered correctly. But not Count_EventReco. I have actually made a number of fields like Eventcode_Reco , made self-joins and created similar fields Count_EventReco. Rest of them seem to work fine except for this one field.

 

Could you please help me out here?

Thank you.

 

Prith

Share this post


Link to post
Share on other sites
Ugo DI LUCA

Hi,

 

Give us an example of an Eventcode_Reco you're using such as

 

Event0005English....

 

What is the structure of EventCode_Reco

 

Also, create a portal using your selfjoin and drop some fields in it to see if any related records appear.

 

As a start, change your calc to Event Code & " "&Recommandation (notice the space, as I'm feeling as the Legth of your key is the problem)

 

HTH

Share this post


Link to post
Share on other sites
Prith

Hey Ugo,

Thanks a million, for all the patience. I put a portal as you asked and saw that I HAD made a mistake!! Got confused in that maze of relationships . I have all tables (and so many table occurences) in one file and its just too crowded!

Thanks again.

Prithvi

Share this post


Link to post
Share on other sites
Ugo DI LUCA

Glad you fixed it.

 

However....

 

Originally posted by Prith:

[qb] Hi,

 

I need to calculate number of records from a particular department, number of records from people with a particular Job title etc. So I made a lot of self joins with different fields..... I did this for all the combination of fields of which I needed a count. [/qb]

What is the max number of fields in these concanation, because I think you'd really gain in having a Multikey on the Right side, specially if you're using these Counts occasionally only, and not all at once.

 

Having so many SelfJoin could drastically slow down your db speed with v6.

I suspect relationships in v7 to be inactive unless they are used (therefore less cpu consuming) but I have no exact proof at the moment.

Meanwhile, I'd use a MKey for the right side and use global concanation for the left side, to toggle from one count to the other.

Share this post


Link to post
Share on other sites
Prith

Okay,

I have a different related problem.

I have a layout based on Event with EventCode as the primaryID. Event and Evaluation are related by Event code. This layout has EventCode field from Events and a portal from Evaluation giving the Counts. this portal looks like this, i.e. the fields are:

Job title : Count_EventJobtitle

Department :Count_EventDepartment

Recommendation :Count_EventReco

and so on..

So when I choose "Professor" from the pop list in field Job Title, I get Count of Professors for that Event in Evaluation, in the Count_EventJobtitle field. and similarly I get other Counts.

 

My problem being here that I want this portal to be used to view the counts only. I dont want to choose "Professor" and that be stored as a new record in Evaluation. If I uncheck "allow entry of field" in Field Behaviour of each field in the portal, I cant choose a value for the Job Title or Department field.

 

I tried changing the layout setup to show fields from Evaluation now, instead of Events, and then do a Find. So when I choose Professor in Job titile, I get the count. But as find mode clears all the fields, I have to enter the Event Code too each time.

 

Is there a way to enter find mode and keep one field constant, i.e. not clear it?

 

Thanks.

Prith

Share this post


Link to post
Share on other sites
Prith

Hi Ugo,

 

You are probably right. I read the Mkey suggestion you had made (to a post I had made earlier) and didnt quiet understand a few things and gave up!

I am using FM7 and the whole thing is very slow.

I guess I'll just try Mkey and get back to you with more qs., mebbe in 2 days!! I hope you'll have time to answer my questions then. smile.gif

 

Thanks again.

Prith

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