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

Problems with calculation fields


ns1025
 Share

Recommended Posts

I have a lot of calculation fields on a layout that I created for a customer.

They are hidden transitory values, so I made them really small and set the text color to white.

The fields were entered when some records already existed. They were working fine, but when I deleted all records to leave the database ready to be started from scratch, suddenly when I tried to enter a few new records they weren't showing any results anymore.

 

Instead of numbers it says "case(" across or behind of where the little field values should appear.

(Although, the "case(" function thing could also have something to do with a global text variable near by)

 

I really need those calculation fields to work for a report for my customer.

 

I tried already...

To expand the fields to give the numbers more room.

Delete or cut the existing boxes and drag in new unused calculation fields of the same type.

 

I really don't know what I am doing wrong and would appreciate some good advices. Maybe some of you encountered the same problem.

 

Thanks,

Nathalie

Link to comment
Share on other sites

Hi,

 

it sounds strange, it's as if part of the calculation formula suddenly shows in the fields. It could be that the file got damaged when you deleted the records.

 

You could try making a new version of an empty file from a known good backup; use the "clone" function to create an empty file.

 

kjoe

Link to comment
Share on other sites

Is there really no way I could fix that. I have an earlier backup, but I didn't back it up right before I deleted all records. That was a biggy!

 

By the why the case function was not part of the calculation in the calculation field.

 

I keep my hopes up...

Link to comment
Share on other sites

Really hard to say. You could try the Restore function, but a restore procedure usually involves not just restoring the file, but then exporting the restored records to a known-good backup.

 

I'd be glad to take a look at the file if you email it to me. There's a slight chance I can see what's wrong.

 

kjoe

Link to comment
Share on other sites

I saw symptoms like that once when a customer had file sharing on on the FileMaker Server computer and people were opening the solution by double-clicking the file via the Finder (or whatever the Windows equiv...Windows Explorer?). File sharing on the Host or Server corrupts shared files. (Particularly w/FmServer deployments).

 

Dunno if that's relevant to your situation.

Link to comment
Share on other sites

I saw symptoms like that once when a customer had file sharing on on the FileMaker Server computer and people were opening the solution by double-clicking the file via the Finder (or whatever the Windows equiv...Windows Explorer?). File sharing on the Host or Server corrupts shared files. (Particularly w/FmServer deployments).

 

Dunno if that's relevant to your situation.

 

I don't think that a file sharing problem would apply here. I was working on a laptop, far away from everybody else. File sharing was not enabled. Although at the office where they use the file they do file sharing. And yes I opened the application through finder on a mac laptop.

Link to comment
Share on other sites

Well, if you weren't opening the file as a guest of someone else, it's both normal and harmless to open it by double-clicking it in the Finder.

 

Can you post a screenshot of what the layout looks like in Browse Mode? And print out your Field Definitions to PDf and post that as well, maybe?

Link to comment
Share on other sites

I am working on PC's at home and I had to return the laptop, but I have an earlier version of the file on an external HD. I attached it to this post. Look at the calculation fields at the bottom of the timesheet layout. The fields are all lined up (exp. REC, CS, etc.)Hope that helps.

Link to comment
Share on other sites

You are right, it was the wrong file. I am so sorry, I uploaded it in a hurry this morning before I had to go to work.

So this is the right file, again I am sorry for waisting time.

Thanks

Link to comment
Share on other sites

I've got the file.

 

You describe a layout with lots of fields, text color white, very small?

 

I assume you're referring to the layout called "TimeSheet" then. Lots of little fields at the bottom below the buttons, text color set to white?

 

I'm not getting any text-strings saying "Case(" or anything else that looks like fragments of field definitions.

 

FileMaker 6 Developer, MacOS X 10.4.6 also verified under

FileMaker 5.5 Pro, MacOS 9 (Classic environment)

FileMaker 5.5 Pro, Windows95 (VirtualPC)

 

it is still possible that the file is corrupted. The one you zipped, when opened, said it had not been closed properly. (Did you zip it while it was open?)

 

Is the visual-artifact problem consistent? Always appears when you open and view this file on your own screen? Can you take a screen shot and post it?

Link to comment
Share on other sites

Hi

 

I could not see the "case(" printed across the layout or any malfunctions for that matter. Since there are some related file(s) missing I can't test it further.

From what I can see, your solution could benefit a lot from reconsidering the database structure.

 

kjoe

Link to comment
Share on other sites

The calculation fields are all on the bottom of the TimeSheet layout and they are all white because I want to keep them hidden from the users.

 

I don't have filemaker installed on my computer at home. Thats why I can't post a screen shot. My employer expects though that I will fix the problem next time I step to the computer at her office.

 

As I mentioned earlier I don't have the actual corrupted file. What I posted was an earlier version where the calculation fields were still working. Maybe if you delete all the records, you will end up having the same problem as me and see a "case(" written across. But keep in mind I don't know if the "case(" has anything to do with the calculation fields. Chances are that it has something to do with the notes fields (notes1, notes2, notes3,...) which are using case functions. However there is still the fact that the calculation fields are not working now.

 

It would be all fine if I could use this earlier version, but I had made some significant changes for the next version that will all be lost if I have to revert to the version I posted.

 

I will post the other files that are related, if it is necessary.

 

From what I can see, your solution could benefit a lot from reconsidering the database structure.

 

What in my structure should I reconsider exactly?

 

Thanks,

Nathalie

Link to comment
Share on other sites

Greetings Nathalie!

 

I don't see any errant "case(" text in your case, or any other reason for it, but it is difficult to see what's going on. The trouble is that your current structure uses multiple similar fields for all the time entries, requiring complex calcs to total and summarize them.

 

The alternative is a relational design, where you use separate files for each distinct entity. In your case, you'd use one file for Employees, one for the TimeSheet, one for the Task Choices, and one for the TimeSheet Lines. With this design, totalling hours for a particular TimeSheet or a particular Employee can be done with just a Sum() calc over the relationship. Summarizing hours is just as easy, using one or two Summary fields in the TimeSheet Lines file and a columnar report with sub-summary parts.

 

Going to a relational design greatly simplifies maintenance, and opens up new ways to group and summarize the data. For example, you could easily create a layout in the Task Choices file that shows a total or list of the times where that particular task was used.

Link to comment
Share on other sites

Nathalie,

 

I tried to force the "case(" glitch by deleting all records, but it didn't happen. I think the best thing you can offer your employer is that you will post the actual corrupted file to the forum ASAP. I'm pretty certain I'll be online coming monday, and if I'm any judge, so will Ender and AHunter :D .

 

kjoe

Link to comment
Share on other sites

This is probably a silly question, but when you saw the "case(" &etc text fragments showing up in your calc fields after doing the delete, did you exit out of FileMaker and reopen the file to see if they still looked that way?

 

I've seen oddball text spillovers in FileMaker 7/8 that are essentially screen artifacts: opening the ScriptMaker and still seeing the layout I was browsing superimposed on the list of script names, going to the Relationship diagram and seeing all the built-in boilerplate sample data ("customizable buttons", etc) strewn all around the white space between table occurrences...

Link to comment
Share on other sites

Actually, I did not close and reopen the database. Maybe the weird "case(" would have disappeared.

 

Different question.. if I would like to use the corrupted database, can I import the calculation fields from the older working version.

 

Ender, I have already a related database for adding more values to the task value list. I am not so sure if the related database would make summing the individual tasks a lot easier.

 

My customer wanted 10 to 14 lines for different employee tasks in the timesheet layout. Somehow the database has to compare the chosen task with a set task name and then sum the corresponding time in a separate field. For the report I need a summary field (right?) that will keep count of the total of a certain task. Only summary fields can be sorted by month in a part on a report (right?).

Ufff. Don't know if I made myself clear, but I don't see how a related database could help. Or could it? Sorry, this is probably very stupid of me to mention ..

How would the file for summerizing the different tasks look like. Would I still have to have one field for each task, mmmh? Oh Really!

Link to comment
Share on other sites

I'd advise re-asking your latest question in a new thread. More people will see it.

 

I'm not at all sure your database is at all corrupted. You only saw the weird "case(" thingie once — I'd write it off as a screen artifact and keep using that file unless you get recurrent weirdnesses from it.

Link to comment
Share on other sites

I am not so sure if the related database would make summing the individual tasks a lot easier.
Indeed it will.
My customer wanted 10 to 14 lines for different employee tasks in the timesheet layout.
This is a great place to use a portal, since the number of lines is indeterminant, and the order is not particularly important.
For the report I need a summary field (right?) that will keep count of the total of a certain task.
Yes, but you need not define a separate summary field for each task. Just use a columnar report with sub-summary parts, having the summary field be a Total of Hours. Place this summary field on whichever parts you want a sub-total on (it will automatically sub-total by each change in the sub-summary part's break field. You just have to sort the records and show them in Preview Mode or Print the report).

 

You might see if the attached demo sheds some light on this (open the TimeSheet file). The structure is pretty common, resembling a typical Invoice or PO solution.

Link to comment
Share on other sites

Hey guys,

I finally got the corrupted file TimeSheetsAVResources and the related file HiringDataAVResources with a screenshot that shows the relation. I zipped it... smiley_cool

 

Ender, I couldn't look at the file yet, but I have a few questions right away.

 

This is a great place to use a portal, since the number of lines is indeterminant, and the order is not particularly important.

 

So if I use portals, each time I use the portal, is it not just a window to another file that will repeat the same thing over and over ? If I change one portal to a certain value will it not change the other once too?

 

Yes, but you need not define a separate summary field for each task. Just use a columnar report with sub-summary parts, having the summary field be a Total of Hours. Place this summary field on whichever parts you want a sub-total on .

Wouldn't I have to have parts for each task code? What I mean is, wouldn't I have to define some fields to the values of the task codes to define the parts? (For example sort part by Rec, sort another part by CS and so on)

 

Thanks so much for your help,

I might start a new post for the last few replies like hunter suggested, so other people can profit from our discussion.

Link to comment
Share on other sites

Hi,

 

I took a look at the file. What I could see was that the field gName contains a calculation formula. The field is locked from manual data entry, but if you unlock this and enlarge the field you'll see that is the case. How the formula got there I really can't say but file corruption is highly unlikely as a cause. Either you pasted it there yourself by accident (but how could you since the field is locked?) or there is a script step (set field [gname, "XYZ"]) that enters a formula as text rather than the result of it. This would happen if the formula is set between" ":

 

set field[TheField; "if(test;result;default)"]

instead of

set field[TheField; if(test;result;default)].

 

I looked through the scripts but there were a lot of and errors around, so I can't pinpoint the culprit.

 

kjoe

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

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.

 Share



×
×
  • Create New...

Important Information

Terms of Use