You need to have an indexable field on the 'right hand side' of a relationship. Using a global as the secondary key doesn't mean anything - by definition every record will match (or none, I suppose).
Yes of coarse, thanks.
OK I've changed my child field to a non-Global field and entered a 1 into every field. The portal is still showing all of the records though.
So I have my relationship set up as
_pk:Leads X _pkLeads
PortalFilterCalculation (this is my calculation field in my Parent TO) = PortalFilterConstant (This is the field I have entered 1 into)
When I cycle through my leads the calculation is working properly. It's showing a 0 for the Leads that don't past the calculation text and a 1 for those that are. My portal should also be showing the leads where PortalFilterCalculation = PortalFilterConstant (Where 1=1) but it's showing all of them.
What am I doing wrong?
Actually to be more exact precise, what is happening is that as I cycle through the leads on layout the portal shows all of the leads if the PortalFilterCalculation = 1 or none of them if it = 0
Giving every record the value "1" is functionally the same as using a global field. Your relationship is matching records exactly as you designed it to. I don't know your calculation, but suspect that you need to swap the fields and use the calc field on the portal's side of the relationship and the "1" field on the layout's side of the relationship. This, BTW, would then allow you to use a global field for the "1" field though I normally just use a calculation field for this purpose.
Ohhhhhhh I was under the impression that you can only use unstored calculations on the parent side of a relationship?
That is correct. Keep in mind that you have not described your calculation. I didn't even know that it was unstored until this last post of yours. I suggest posting it so we can suggest an alternative method that will produce the results that you need.
Are you using FileMaker 12 or 13? That will make the ExecuteSQL function an option to consider for your relationship level filter.
I am using filemaker 12 advanced
I am basically trying to speed up my database. I got everything working using portal filters, however they were slowing my database down A LOT. I believe that it's because I had to use summary fields to count the records in my portals. I can use Count () when they are filtered by a relationship and that works a lot quicker.
Here's the calculations that I am trying to filter through a relationship
LeadsPortal::AreaCodeFilter = "SHOW ALL"; 1;
LeadsPortal::AreaCodeFilter ="Eastern"; LeadsPortal::Timezone = "Eastern" or LeadsPortal::Timezone = "Eastern/Central";
LeadsPortal::AreaCodeFilter ="Central"; LeadsPortal::Timezone = "Central" or LeadsPortal::Timezone = "Central/Mountain";
LeadsPortal::AreaCodeFilter ="Mountain"; LeadsPortal::Timezone = "Mountain" or LeadsPortal::Timezone = "Mountain/Pacific";
LeadsPortal::AreaCodeFilter ="Pacific"; LeadsPortal::Timezone = "Pacific" or LeadsPortal::Timezone = "Mountain/Pacific";
Leads::LeadDocStatus ≠ "Dead Deal" and
Leads::LeadDocStatus ≠ "Docs In" and
Leads::LeadDocStatus ≠ "Deal Funded" ; 1;0
((LeadsPortal::CallBackDateMoneyReady ≤ Get(CurrentDate) and LeadsPortal::CallBackTime ≤ Get(CurrentTime) and
LeadsPortal::CallBackDate ≤ Get(CurrentDate)) or
IsEmpty ( LeadsPortal::CallBackDate ) and IsEmpty ( LeadsPortal::CallBackDateMoneyReady ) )
FYI I got it working with an unstored Calculation field, however, I will have to set up all types of Script Triggers to keep the data current ya?
I'd much rather implement an ExecuteSQL method but don't know much about how to do that.
Actually an unstored Calculation seems to work just fine, every time I modify any of the fields in it's calculation it updates the portal.
What then is the TRUE difference between a stored and an unstored calculation?
I would love to implement an ExecutiveSQL method here instead if that will make my database run even faster?
First, define the following stored calculation field in your table: Name the Field cAreaKey.
PatternCount ( LeadsPortal::Timezone ; "Eastern" ) ; List ("Eastern" ; "Eastern/Central" ; "Show All" ) ;
PatternCount ( LeadsPortal::Timezone ; "Central" ) ; List ( "Central" ; "Central/Mountain" ; "Show All" ) ;
PatternCount ( LeadsPortal::Timezone ;"Mountain" ); List ( "Mountain" ; "Mountain/Pacific" ; "Show All" ) ;
PatternCount ( LeadsPortal::Timezone ;"Pacific" ) ; List ( "Pacific" ; "Mountain/Pacific" ; ; "Show All" )
Next, define cAssociateKey as
List ( 16 ; __fkSalesAssociate )
For both of these calculations, select Text as the result type
Define cCallBackTS as:
If ( IsEmpty ( CallBackDate ) and IsEmpty ( CallBackDateMoneyReady ) ; TimeStamp ( 1 ; 0 ) ;
TimeStamp ( CallBackDate ; CallBackTime ) )
Select TimeStamp as the result type.
Define cMoneyReadyKey as
If (IsEmpty ( MoneyReady ) ; GetAsDate ( 1 ) ; MoneyReady )
Select Date as the result type
Define cTodayTS as an unstored calculation: Get ( CurrentTimeStamp )
Define cTodayDate as an unstored calculation: Get ( CurrentDate )
Now you can construct a multiple field self join relationship:
LayoutTO::AreaCodeFilter = PortalTO::cAreaKey AND
LayoutTO::cTodayTS > PortalTO::cCallBackTS AND
LayoutTO::cDeadDocStatus ≠ PortalTO::LeadDocStatus AND
LayoutTO::cTodayDate > PortalTO::cMoneyReadyKey AND
LayoutTO::__fkSalesAssociate = PortalTO::cAssociateKey
Now that's Really complex. I can't be sure that I nailed down every possible nuance of your original calculation.
Unstored calculation fields cannot be used on the portal (child or "many") side of the relationship. They can be used on the Layout (Parent or "one" side of the relationship)
Thank you SO MUCH for taking the time to put this together. I am going to go through it and see if I can get it to work. I actually got some of what you listed here done so far so I must be figuring this stuff out a bit:)
Do you know a good resource for me to learn more about incorporating ExecuteSQL into my filemaker databases?
So far I got a few of these working, however I don't understand exactly how they are working.
with regards to setting cAssociateKey = List ( 16 ; __fkSalesAssociate )
(I related it to my global sort field SalesPersonSort instead and it work, I think that's what you meant to put there)
But I don't understand how listing 16 AND _fkSalesAssociate is making this relationship work. I know that the list basically creates an array but I thought that each item in the ARRAY created AND not OR . Is it basically saying SalesPersonSort=16 OR SalesPersonSort=_fkSalesAssociate ?
Okay got them all working and selecting each portal row, that has a GoToRelated Record Script assigned to it, is noticably faster!
If I change any of the dates in my main layout I get "Find in Progress - Processing Query" Which takes a good 5-10 seconds
So I'm back at square one with speeding this thing up. I thought that If I filtered this portal with relationships only that it would be zippy again... grrrrrrrr