Jump to content
Andrew Hubbard

What is the difference between a calculated "1" & a typed "1"?????

Recommended Posts

Andrew Hubbard

I'm a FM user of many years but only recently am I devoting more time to it so it may better serve my business. I've got a problem and i can't work it out.

I have a solution of many different tables and I recently created a dashboard to bring certain information from each of the tables to a central location. The way I have done this in by creating another "dashboard" table with a single record and using portals and match fields. the match field on the dashboard is constant, the match field in the related table is dynamic. simple right? Oh no!

The dynamic match field is a calculation field, it doesn't store the result and is constantly calculating so that when the criteria the calculation is dependant on changes so does/can the result, these are mostly dates so at a point "X" number of days before a certificate expires the tables match field changes from a 0 to a 1 thus matching the dashboard match field and appearing on the dash board.  This worked like a dream....... to start with!

So that's the background explained.

I have two problems....

1. The dashboard isn't automatically updating, by that I mean the table's match field in the related table is changing as the dates change but it doesn't appear on the dashboard as when it should. I went to the related table changed the date then changed it back again and this seemed to do the trick. Not the way it should be but I can work to resolve that and then the other day everything disappeared from a couple of my dashboard portals! someone has changed something right? Nope!

2. Two days to figure out nothing had changed at all, except FM willingness to accept the result of a calculation as a valid match for the related tables fields, allow me to explain, the result of the calculation is a "1". here's the calculation "If ( FieldName <  Get ( CurrentDate )+40; 1 ; 0 )" simply enough. As the current date gets closer to the date in the record the match field changes from a "0" to a "1" at 40 days and it does do that, that isn't the problem the problem is the portal will not work! I originally thought I'd altered something that the related tables or fields were dependant on but I hadn't! I did the visual check on the related match fields in both tables both read "1" but no data so I checked everything I could think of deleted the relationships, set them up again and again and again it just wouldn't work it was driving me nuts! eventually I changed the field type from a calculation field to a text field and manually insert the "1"  and that works! The data appears in the dashboard portal perfectly! 

Why, what is the difference between a manually typed 1 and a calculated 1, I've checked the use of spaces in the calculation result and eliminated that. What is happening apart from this sending me mental?

 

 

Share this post


Link to post
Share on other sites
Josh Ormond

You need to double check and be sure that the field on both sides are resulting in the same data type. Calculations default to result in a number, typically. You can change it. Make sure it’s the same on both sides. 

  • Like 1

Share this post


Link to post
Share on other sites
AHunter3
Quote

If ( FieldName <  Get ( CurrentDate )+40; 1 ; 0 )

You've got a problem.  

 

There are two storage methods that are applicable to formulas in calc fields that rely on CurrentDate:  stored (with the option of indexing) and "do not store calculation result -- recalculate when needed".

A stored calculation field does not 'notice' when the day rolls over.  What it stores is the outcome of the formula at the time it evaluates it.  It only re-evaluates it if the value of a referenced field ( e.g. FieldName in your formula here) gets changed.  

An unstored / recalc-as-needed field reflects the current value, re-evaluating whenever it needs to display.  Problem is, you can't use an unstored calc field as the foreign side of a relationship, because it can't be indexed.  

 

 

  • Like 1

Share this post


Link to post
Share on other sites
Andrew Hubbard

Thank you AHunter3, very much appreciated. 

Ok that given is there a way in which I can force by way of a script  a relook-up or refresh of portal or any other way to get this to work?

 

not sure I explained myself properly with this one. the calc field (lets call it "CalcA" for this explanation) needs to be fulid/dynamic as it also serves as the match field for a portal on a dashboard set up, as the days click by at a certain point the calc result changes from true to false, triggers the match and appears, as if by magic, on the dashboard. could A create a second and make that equal to "CalcA" and perhaps use that the match field.

 

or is there a way that you now of to achieve this result.

 

thanks again for your help it is greatly appreciated. 

 

Andy

Share this post


Link to post
Share on other sites
AHunter3

There are ways, but I hesitate to recommend one without knowing more about your database and your environment.  Just as a throwaway example, you could have a date field in every record and, on firstwindowopen replace its contents with today's date.  Then reference that field instead of Get(CurrentDate) in your formula.  Still wouldn't notice when midnight came—accuracy would depend on the notion that everyone quits out of the database and goes home at 5 PM and comes back to work the next day.  Also isn't practical if you've got an enormous number of records or a slow network connection.

Share this post


Link to post
Share on other sites
Andrew Hubbard

Hi AHunter3

thanks for getting back to me. 

A bit of back ground. My company manages houses less than 200. I have several tables for the different elements of the business. I have the following tables... houses, residents, customers, repairs, vehicles, manpower and a couple of others. None of these have lots of records, residents is the biggest with just over 600 records but that because data on old residents from years gone by are stored there. There is one further table which is the dashboard, which has one records and several portals on it, I have several match fields on this table one for each portal I.e HousesMatchField, VehicleMatchField, etc. 

We have 6 people accessing the database and as they go about there daily tasks they change the condition of things and they appear or disappear from the dashboard, an example would be repairs, when they log a repair on the system the status of that repair is automatically “pending” which causes the match field in repairs to set to “1” which is a match to the dashboard  repair portal and the record shows in the portal, when the repair status changes to “complete” the repairs match field changes to “0” which is no longer a match. That system works absolutely fine because it’s a manual thing that people do during the course of their work. Now here comes the problem area with the “GetCurrentDate”. If we look at vehicles they need to be serviced on a date in the future which is set in the vehicles table months before it due and as that date approaches I need the match field status to change and for it to appear in the dashboard, people do not routinely visit the vehicles table.

Then we have the houses table, this has several match fields on it for which there are several portals on the dashboard but let’s talk about the gas safety certificate match field, every year each house must be checked to make sure it is gas safe, this is a legal requirement in the uk. So 30 days before the due date the match changes and the house appears on the dashboard, the test gets done and then someone goes into the house record and changes the next test date to one year after the test that was just done and it disappears from the dashboard until we get to 30 days from that test date and so the process starts again. We have the same situation for electrical tests also. As you will appreciate 200 properties means on average 15-16 properties each month should come and go from the dashboard. 

 

Share this post


Link to post
Share on other sites
AHunter3

With such small tables, I think I would indeed make a script that performs a Replace Field Contents on a local stored date field (with today's date); make that script run when the db is first opened; and change all field defs that currently reference Get(CurrentDate) for your dashboard relationship so that they reference that stored local date field instead.

  • Like 1

Share this post


Link to post
Share on other sites
Andrew Hubbard

Thanks AHunter3 That’s a brilliant idea thanks, your help is much appreciated. Without your help it may have taken me a long time to merely establish why it wasn’t working let alone produce a fix so thanks again. 

I do love FM as there’s is always more than one way to do things. I must say this though without people like you, this forum and the YouTube tutorials that helps us little guys out life would be so much harder. 

Thank you and all that have chipped in on this problem. 

No doubt we’ll chat again. 

Regards

Andy

Share this post


Link to post
Share on other sites

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.




×
×
  • Create New...

Important Information

Terms of Use