Unstored calculation based on related field on different host

[sophiasys]

Hi there,

 

I have a system which uses the data separation model. The interface is on the local computer, and the database hosted on filemaker server.

 

Within a crew table, I have the fields FirstName and LastName. A FullName calculation then joins these together, which is unstored.

 

Several crew can be on shift at anyone time, managed through an OnDuty join table. A summary field gets a list of all the On Duty crew for each shift, and then a separate calculation field removes the carriage returns and replaces them with commas, named OnDutyCrewCSV.

 

When running on the local system, this works perfectly.

 

However, Ive just uploaded the database file to the server, and now the fields are blank. If I create a new shift, these fields are calculated. They calculation doesn't seem to run when the database is first opened though? If I close the file and reopen, I have to repeat the above process...

 

Can anybody shed any light on this please?

[LaRetta]

Within a crew table, I have the fields FirstName and LastName. A FullName calculation then joins these together, which is unstored.

 

Hi Sophia,

 

A FullName calculation should be stored and not unstored.

 

Several crew can be on shift at anyone time, managed through an OnDuty join table. A summary field gets a list of all the On Duty crew for each shift, and then a separate calculation field removes the carriage returns and replaces them with commas, named OnDutyCrewCSV.

 

This does not quite make sense to me; probably because I am unclear on your use of this calculation and your current structure, nor do I understand why you call the calculation OnDutyCrewCSV.  Are you exporting this calculation?  

 

If you create a self-join of the OnDuty join table, joining thus:

 

OnDuty::Shift = OnDuty 2::Shift

 

... then create a calculation which is:

Substitute ( List ( OnDuty 2::FullName ); ¶ ; ", " )

... this should provide you what you wish.  And welcome to FMForums!!  

Note that the OnDutyShift 2 table occurrence is only a visual replica of your original OnDutyShift join table and not a separate table.  To create it, just select the ++ in the graph after selecting your OnDutyShift table.  This will create the duplicate representation for you to use.

[sophiasys]

LaRetta,

 

Thanks for your reply.

 

How come Full Name should be stored? Is this not repeating data which is already held?

 

With regards to the substitution, thats exactly what Ive entered and this works when both files are on the same machine. When I move the database to the server though the field is blank?

 

BW,

 

Sophia

[sophiasys]

If I add a new shift, and then delete it without adding any details, then the calculation fields are populated, as with the value list based on Full Name.

 

It seems to be a problem with when the calculation field is evaluated on the server, compared to the local host?

[Lee Smith]

Is this an Auto Enter Calculation or regular calculation in a calculation field?

 

Why not post a copy of your file and save some time? Here is a link that explains why it will be helpful, and the steps involved to upload a file. ATTACH A FILE

[Rick Whitelaw]

If it's a calculation field and can be stored it's best to store it. This way FM doesn't need to evaluate it constantly. As well, the calc is being evaluated the same way whether the file is local or hosted so it seems odd it wouldn't evaluate when hosted.

[LaRetta]

How come Full Name should be stored? Is this not repeating data which is already held?

 

I have hesitated responding hoping instead you would just try unchecking 'unstored' on the Full Name.  Full Name is used in a value list.  Depending upon your setup (which we still do not know), value list (99.5% of time) must be stored to work.  Since I know nothing about your setup nor about your value list definition etc, I can not provide you the principle in why it is failing - only seeing the file or asking a dozen questions could do that.

 

Some examples of why we cannot help you pin the reason it breaks:

1. Is the value list in the UI file or in the data file and referenced using 'use value list of another file'?
2. Is the value list based upon an ID with the Full Name as the second field or are you (incorrectly) using the FullName as the primary value?
3. Do you sort on first field or second (FullName)?
4. Do you display the calculation on your layout or simply export it?

... and the list goes on.  Simply, changing the FullName to stored will probably fix the problem and it is an easy test for you to find out.  :-)

[sophiasys]

Hi, 
 
Sorry for the delay in replying. Ive just come off a run of night shifts. 

 

FullName was stored all along I've just realised, as it indexes on this field also. So sadly that wasn't the root of the problem.
 
Ive tried to strip back my solution to post online, but can't get it to work. Because the solution was designed to hold patient confidential information, theres a lot of behind the scenes scripts which ties the software to certain computers etc.
 
Ill try and explain step by step with pictures, but I appreciate that this makes things more difficult to troubleshoot.
 
FullName is defined in the Crew Table, stored, as a calculation.
The value list is based on the pk_CrewID field which is generated on creation using a UUID. The FullName field is shown to the user instead of the pk.

 

 
There is a many to one relationship between "on duty" and "crew" tables, and between "on duty" and "shift". 
 
Here are some images of the Shift, Crew, and On Duty table definitions.

 

 

 

 

And here is the relationship between the files. The "CREW | On duty Medical" and "CREW | On duty Pilots" are instances of the crew table, but filtered to only include pilots, or only include medical personnel. 

 

 

Everything works perfectly when both files are hosted locally. When I move the database file to the server, the names on the recent shifts section are blank until I create a new shift, then the fields are all populated correctly. The value lists which are based on the crew name also show the primary key when hosted on the server, until a new shift is created. 

 

 

Any hints or tips would be hugely appreciated. I realise this is difficult to troubleshoot without having the file. If its completely impossible, Ill try to strip out all the business logic and patient confidential information and post it online.

 

A massive thanks for your help so far.

 

BW,

 

Sophia

[sophiasys]

* Update *

 

Specifically, the field is populated when the On Duty layout is opened. This is just a table view of the On Duty table, with no script triggers.

 

Ive tried runnning a script to navigate to this layout, and then back again, but this didn't work. I have to manually go to view, go to layout, On Duty, then navigate back again. 

[LaRetta]

Hi Sophia,

 

Summary fields are based upon the found set so calculation using it in the Crew table seems incorrect.  The calculation On Duty Crew Names CSV, listing the crew members on duty should be in the On Duty table.  What if you shift perspective here, and in On Duty table, create two calculations:

 

For on duty medical:

Substitute ( List ( Crew | On duty Medical::Full Name ) ; ¶ ; “, “ )

for on duty pilots:

Substitute ( List ( Crew | On duty Pilots::Full Name ) ; ¶ ; “, “ )

The calculation will be naturally unstored.  If this doesn't work then we will need to see the file (it can be empty zipped clone) or possibly others can see the problem more clearly.

Edited March 20, 2015 by LaRetta