Export calc field issue

[Kris M]

I am trying to do an export from FMP to excell file.

script prototype...

go to layout for export

find data

go to browse

export records

The script functions perfectly with one exception...

Their is a field on the layout that is a calculation field that is not exported.

The calc field has valid correct data.

The calc is

If(field 1 = "Yes" and field 2 = "Yes";

Case ( cal_interval_units = "Day(s)";last_cal_date + cal_interval;

cal_interval_units = "Week(s)";last_cal_date + (cal_interval *7);

cal_interval_units = "Month(s)";last_cal_date + cal_interval * 30;

cal_interval_units = "Year(s)";last_cal_date + (cal_interval * 365);

"Error"

)

)

It is set up to return the date datatype, 1 repetition, do not eval if all ref fields are empty is unchecked, and is storage type unstored.

Can anyone tell me why this field is not exported?

PS the calc field is in the table that the export layout originates from so it does not appear to be a context issue. The layout/field displays correct data in browse mode

[jamesducker]

I don't know, and would suggest you post a sample file. Meanwhile, two things come to mind:

1) the "months" line doesn't have brackets around the cal_interval * 30

2) the word "Error", if that is what is returned, is not a valid date... so does not fit in with your choosing date as the result.

Hope that gets you a little bit closer... if not, please post a sample file.

James

[comment]

the "months" line doesn't have brackets around the cal_interval * 30

Nor does it need any - and the parentheses in the other lines are entirely redundant.

In any case, neither this nor the "Error" part would cause the field to be excluded from export. I suspect it's simply not included in the export script step to begin with.

[jamesducker]

Indeed, the brackets are technically redundant... I should have made it clear that I was pointing out an inconsistency rather than a fault. (Personally, I fall completely in to the pro-brackets camp, and would put them in anyway for clarity.)

If all the records being exported happened to return "error" (I suspect 'cal_interval_units' is global so they would all return the same), it is possible that nothing would appear in the export, hence giving the impression that the field was missing altogether.

We could clear it up definitively and easily if we had a sample file.

J

[Kris M]

Holy crap that was a stupid mistake.. I was confusing save to excel where only the fields on a layout will be exported and export records where the exported data is controlled by dialog. Thanks for kicking me in the head..

[comment]

If all the records being exported happened to return "error" (I suspect 'cal_interval_units' is global so they would all return the same), it is possible that nothing would appear in the export

No, they would all export with a question mark as the contents of the field.

Personally, I find redundant parentheses add more confusion than clarity: the purpose of parentheses is to change the default order of evaluation; if the order remains unchanged, then you're left wondering what the parentheses are supposed to do.