Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Weird behavior with calculation used in relationship


devinriley

Recommended Posts

Let me preface this by saying that yes, I realize you can't put a calculation on the child/many side of a relationship. Unfortunately, the system I'm working with was set up by another person using this method several years back and hadn't really been noticed until now. The strange part and reason for this is that in a number of cases, which is what's baffling me, you CAN see the child record across the relationship.

 

So here's the table structure I've got. The main table is called Cages and the child side is called Mice. This is a one to many relationship. On the parent side, the relationship was set up as CagePK and Constant(1). Both are indexed numbers. In the Mice table they relate to a CageFK and cBreedableBool, respectively. The cBreedableBool is an unstored calculation that evalutes to a 1 or 0 based on other unstored calcs, and always evaluates. This is obviously a problem and bad design.

 

This has been in use for about 4 years, and somehow hasn't come up til now even though this gets used daily. I'm looking for ways to change the structure, but I'm really curious to see if anyone on here can explain why it picks up correct records on the child side about 80% of the time and others can't evaluate. I can go back and forth between two cage records with almost identical Mice assigned to them, and yet on one Mice_CagePK_cBreedableBool::MousePK will be filled in and on the other won't, when cBreedableBool evaluates to 1 for both Mice. How can this happen?

 

My thought for doing this correctly and getting these results is to simplify the relationship to CagePK=CageFK and then make a second table occurence of Mice called MiceBreedable, and make it relate to itself. From there, I'd set a relationship of MousePK=MousePK AND cBreedableBool=Constant. That puts the calc on the parent side and should get me the exact same set of results, no?

Link to comment
Share on other sites

Let me preface this by saying that yes, I realize you can't put a calculation on the child/many side of a relationship. Unfortunately, the system I'm working with was set up by another person using this method several years back and hadn't really been noticed until now. The strange part and reason for this is that in a number of cases, which is what's baffling me, you CAN see the child record across the relationship.

 

That's because it's untrue that you can't put a calc field on the child/many side of a rel. You just can't allow creation of new recs on the child side via the relationship if you've done it that way, you'd have to script the creation of new records.

 

 

So here's the table structure I've got. The main table is called Cages and the child side is called Mice. This is a one to many relationship. On the parent side, the relationship was set up as CagePK and Constant(1). Both are indexed numbers. In the Mice table they relate to a CageFK and cBreedableBool, respectively. The cBreedableBool is an unstored calculation that evalutes to a 1 or 0 based on other unstored calcs, and always evaluates

 

"Always evaluates" is not a characteristic of a calculation field. It's a characteristic of a non-calculation field with an auto-enter calculation option. What kind of field do you have here? Unstored calc strikes me as impossible. Can't be.

 

An unstored calculation absolutely will not work for the child side / target side of a relationship, or as one of many fields on that side of a relationship. The fields on the remote (target, child, etc) side have to be indexed in order for the local side to reference the related records in any fashion.

 

You can have one or more calculation fields on the child side of the rel (bad idea though you may consider it to be be; FmPro will let you do that and it will work), but they must be indexed.

 

My thought for doing this correctly and getting these results is to simplify the relationship to CagePK=CageFK and then make a second table occurence of Mice called MiceBreedable, and make it relate to itself. From there, I'd set a relationship of MousePK=MousePK AND cBreedableBool=Constant. That puts the calc on the parent side and should get me the exact same set of results, no?

 

That sounds entirely workable.

Link to comment
Share on other sites

Clarification: I shouldn't have said Always Evaluates, as you are correct it's misleading. I meant to say that it's an unstored calculation and the checkbox "Do not evaluate if all referenced fields are empty" is unchecked, so it should always return a value. Sorry for the confusion.

 

An unstored calculation absolutely will not work for the child side / target side of a relationship, or as one of many fields on that side of a relationship. The fields on the remote (target, child, etc) side have to be indexed in order for the local side to reference the related records in any fashion.

 

This, I believe, is what I was getting at. cBreedableBool is an unstored calculation and is one of the two keys used in the multi-key relationship. That's what I meant by realizing I can't use the calc in that capacity, being that it's on the child side.

 

In practice, doing so should make it fail and find no records, but in some cases it's working and letting me see data in the mouse table. It shouldn't be letting me see that field from within the cages table(layouts) ever based on my understanding. Since I can't get to the mouse record unless it's already evaluated to 1, and that can't happen unless I can get to the record, we shouldn't find the related records. I'm simply trying to understand what is either different or happening on some of these records to produce seemingly impossible results.

 

Still going to redo it my way, as this is bad practice anyway. It's just one of those things I'd like to know, as I hate it when things happen that I can't explain or understand.

Link to comment
Share on other sites

In practice, doing so should make it fail and find no records, but in some cases it's working and letting me see data in the mouse table. It shouldn't be letting me see that field from within the cages table(layouts) ever based on my understanding.

 

Mine, too. Totally. Now I'm quite curious as to how it could ever, under any circumstances, display a related Mouse record.

Link to comment
Share on other sites

  • 1 year later...
When I make a new record and input data in the first table. The data is not filling in on the other nine tables unless I inter something in one of the fields on each table.

 

Yes, that's the way relational databases work.

 

Imagine that you were in FileMaker 2. You have two database files, Companies and Invoices. You could write a script that would COPY the Company ID and then call a remote script in Invoices to create a new record and PASTE the copied Company ID there, and you could make a batch of other fields in Invoices be lookup fields that would fill in values from Companies; then you would type in the Invoice information. But you would not expect a new invoice record to just "leap into existence" merely because you had created a new Company, would you?

 

Fast Forward to FileMaker 4. Because you are now in the modern world of 1998 (;)) you get rid of all that arcane "Copy" and "Paste" stuff and you now have a relationship between the Companies file and the Invoices file. You put a portal to Invoices right there on the Companies data entry page. Whenever you want to create a new Invoice for your company, you just go to the bottom row of your portal and start typing and it instantly makes a related Invoices record. (It does that because you checked the checkbox in that relationship to allow record creation in Invoices). But if for some reason you did not want to use a portal, you would have to script the creation of related records: perhaps using Set Field to set a global field over in Invoices to the value of Company (using a universal relationship to Invoices, where some constant value like "1" or "anyRecord" matches "1" or "anyRecord" over in Invoices) and then calling a remote script over in Invoices telling it to make a new record and then set Company to the value of the global field.

 

Now hop forward another decade and you're in FileMaker 9 or 10, I assume. Surprisingly little has changed since FileMaker 4 as far as creating related records, really. You can now have a Companies TABLE and an Invoices TABLE all in the same database FILE; You have a lot more tools and techniques available at your disposal; but basically you can still either put a portal to Invoices from Companies (and check the checkbox in the relationship to allow record creation on the Invoices side of the relationship) and create your new Invoices by simply typing in the empty bottom row of the portal; or you have to script the creation of new records in Invoices almost the way you would have even in FileMaker 2, by capturing the value of the Company and then making an Invoice record and dropping the value of the Company into a field in Invoices. Just as you no longer needed COPY and PASTE in FileMaker 4 you don't need global fields and a special universal relationship in FileMaker 9 or 10; and you can do it all in one script now, you no longer need to call a remote script in Invoices because we're all in one file now: Just set a Variable to the value of the Company, go to an Invoices layout, make a new record, then use set field to set the value of the Company to the value of the Variable you created.

 

Set Variable [$CompanyID; Company::CompanyID]

Go to Layout [invoices (Invoices)]

New Record/Request

Set Field [invoices::CompanyID; $CompanyID]

Commit Records (no dialog)

 

 

 

 

Two: some of the calculation fields are showing an "?" mark in place of the totals.

 

Could be number fields that have a value too long to display in the available space. You can format the field in Layout Mode to round to a specified number of decimal places, or make the field wider, if that's the problem. Or it could be that the result of the calculation makes no sense and FileMaker is shrugging its shoulders at you. What is your calc formula? If you have a calculation field of result type "number" defined as Customer Name + 15, that would most likely give you a "?".

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use