1 2 Previous Next 21 Replies Latest reply on Sep 25, 2014 10:04 AM by BruceRobertson

# Calculation failing for one record only (fine for all others)

Hi,

I have formulated a calcuation to add the number of shelves belonging to a particular department in a particular room to give rise to the total number of shelves belonging to the one said Department:

Eg.

Room A

User 1 (Dept A): 6 shelves

User 2 (Dept B): 1 shelf

User 3 (Dept A): 2 shelves

User 4 (Dept A): 2 shelves

The total that the calculation should give for Dept A is: 10

My formula is described below:

Sum ( If ( \${Admin CERCAR Requests }::Department = "DeptA" ; \${Admin CERCAR Requests }::#DeptA_Shelves ; 0 ) )

The above formula works for all records bar one. There is nothing different about this record. It was created exactly the same way as the others and holds nothing special.

Can anyone offer any suggestions as to why the issue is arising?

Thanks,

-Karen

• ###### 1. Re: Calculation failing for one record only (fine for all others)

Not sure there is enough information about what you are trying to do, but... the way you are using the Sum function seems incorrect. You should not have the "If" string inside the Sum... Also the syntax for the "If" you are using does not look like anything I have ever seen, and looks completely wrong to me.

I am not sure what a Local Variable with the text in curly brackets being listed like a table occurrence means, but unless there is something I am missing, this string does not seem like any kind of valid FileMaker syntax I have ever seen or understood.

• ###### 2. Re: Calculation failing for one record only (fine for all others)

Here is a link to the PDF of the FileMaker 13 Function Reference.  This may help in better understanding the syntax of functions.

https://fmhelp.filemaker.com/docs/13/en/fmp13_functions_ref.pdf

• ###### 3. Re: Calculation failing for one record only (fine for all others)

Hi,

Thanks for your comment.

I shall break it down without the "if" string and see how we go.

The formula, works, though - so I'm not sure if this is the problem.

• ###### 4. Re: Calculation failing for one record only (fine for all others)

Re "The above formula works for all records bar one".  It could be that this one record is the only one that really tests the formula. In other words, it may actually only appear to work for other records because they deliver what appears to be your expected result in spite of it.  That said, I agree with wsvp's comment that it looks llike wrong syntax, although I can't put my finger on exactly what's wrong.  Why don't you post a copy of your file so we can take a look at the formula in place?

• ###### 5. Re: Calculation failing for one record only (fine for all others)

Yes.  This may be correct!!

I've arrived at the result in a convoluted way as I'm using information from two related tables to give me what I want.

I have attached two images.

The first is of the table from which I did my initial calculations - the Request Table.

Take B.4.48 as an example - the first two recrods.  Two people have requested this room, each are using 9 shelves and both are from the same department.  When you look at the second image (RoomLayout), the Total number of shelves (which currently reads as 520.75 in the Request Table), shows up as 18 (9+9) in the Room Details layout (which is what i want).

Now.  What I want is for the absolute total - the 520.75 to appear in my Room Details layout as I want to be able to see how much a particular department is using at any given time (and how much space is left for them to occupy).  But in my layout, I can only see the total that is specific for that particular room - not grand totals of everything is use.  The blue circle shows where I would like to see the grand total usage for a particular department.

I hope this makes some sort of sense......

• ###### 6. Re: Calculation failing for one record only (fine for all others)

Further to the above, the only way that I was able to get the grand total to appear in the RoomDetails layout was to use the formula initially queried (I did this in the RoomDetails table).  It worked for all bar one of my records so my grand total was short by 6 shelves.  This, ofcourse, is not acceptable.

The yellow box above the red circle was the field i needed to create my new formula in the RoomDetails table (this calculation was also done in the RoomDetails table).  I checked whether the two fields matched and all did bar the one (where one had a "6" and the other "0").

• ###### 7. Re: Calculation failing for one record only (fine for all others)

\${name} is not local variable. This syntax is used for table or field if the name is not valid in expression since it is starting with number, containing some chars like '+', etc.

• ###### 8. Re: Calculation failing for one record only (fine for all others)

I'm not sure how everything is set up, but the ExecuteSQL function can be used to get subtotals.

It would be something like

ExecuteSQL ( "

SELECT SUM ( FieldYouWantToTotal )

FROM TableOccurance

WHERE Conditional

; "" ; "" )

I don't know if this is what you need but looking at your original post it might be:

ExecuteSQL ( "

SELECT SUM ( /"#DeptA_Shelves/" )

FROM /"Admin CERCAR Requests/"

WHERE Department = 'DeptA'

; "" ; "" )

For the more information on using the function and the proper sytax see the link below:

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

And dude, naming standards.

• ###### 9. Re: Calculation failing for one record only (fine for all others)

I have no standards.......I actually thought I was doing alright!!!!

Thanks heaps for the tip - will give it a go now!!!!

• ###### 10. Re: Calculation failing for one record only (fine for all others)

ccbt

I've had a look and I just can't get my head around it!!!

i've actually identified the issue.

My calculation stops when a department (ie. Dept B) other than the one it is searching for (ie. Dept A) is hit - so the tally is inaccurate.

Example:

Room 1

Dept A - 2 shelves

Dept A - 3 shelves

Dept B - 2 shelves

Tally (Dept A) = 5 (correct)

Room 2

Dept A - 3 shelves

Dept B - 2 shelves

Dept A - 2 shelves

Tally (Dept A) = 3 (incorrect)

Any suggestions as to how I can tell it to find and add everything that matches?

• ###### 11. Re: Calculation failing for one record only (fine for all others)

Is this calculation in a script or a field definition?

• ###### 12. Re: Calculation failing for one record only (fine for all others)

A field definition.

I changed my calculation slightly. It now reads like this:

If ( \${Admin CERCAR Requests }::Department = "DeptA" ; Sum(\${Admin CERCAR Requests }::#DeptA_Shelves) ; 0 )

As I mentioned, it goes through all the rooms but once a different department is encountered in a room, it jumps to the next room - resulting in an inaccurate tally for the said room.

By chance, all of the rooms in my DB list Dept A as the first request - all bar the one that failed.  The room that failed has 2 occupants - 1 x deptA and 1 x deptB. In this room, though, the request from the person from deptA was made after the person for deptB so appears second in the list. The calculation results in a zero tally for deptA because it encountered deptB first.

I find this really strange - is there a way of telling it to search all records?

• ###### 13. Re: Calculation failing for one record only (fine for all others)

As mentioned by WSVP and keywords, the sum function does not work this way, you are not providing enough information, and it seems highly likely your data structure is not really set up right.

I suggest you give up on the illusion that tweaking your malformed calculation  ( \${Admin CERCAR Requests } what???)  is going to accomplish anything.

What IS your data structure? Is there a separate table of shelf assignments?

As previously mentioned, the "#" character should not be used in a field name. #DeptA_Shelves should just be DeptA_Shelves but really; this field should not even exist.

It probably belongs in a separate table where roomID, department, and number of requested shelves are discrete fields.

• ###### 14. Re: Calculation failing for one record only (fine for all others)

Thanks Bruce.

Okay.

To be clear "( \${Admin CERCAR Requests } " (nor any other part of the formula bar the "logic") is not something I wrote from scratch." Admin CERCAR Requests" is the table in which "department" is located. The components of the formula are comprised of the fields I need FM to looks for - these were all (bar "0") selected from a list and FM decided on how they would be written. I have no answer for your query as I did not decide on how FM would capture my selections (I did, though, name my tables and fields - mis-use of # has now been noted - thank you for this).

So. Moving forward. Perhaps my table structure is not correct. The current structure (with reference to my incorrectly formulated calculation) looks like this:

My calculation (I now realise, it is malformed, but I'll attach it for the sake of clarifying what it is that I want to achieve):

If ( \${Admin CERCAR Requests }::Department = "DeptA" ; Sum(\${Admin CERCAR Requests }::#DeptA_Shelves) ; 0 )

My aim is to get the grand total of shelves occupied by each of the  departments.

My "Admin CERCAR Requests" table captures the department from which the requestor is from and the number of shelves they would like

In my "Admin CERCAR Requests" table, I have separate fields calculating the number of shelves for each of the departments (field name: "DeptX_Shelves") (it basically separates them out)

In my "Admin CERCAR Requests" table , I also have a "summary" field for each of the departments, tallying all the shelves for each.

In my "Admin CERCAR Requests" table, the numbers look fine. The different requests have been categorized and the summary total is correct.

BUT, I need for this information to appear in my "Room Details" layout (different table - related).  Each of the records in  Room Details" table represents a specific room. When I select the "summary" field directly from the "Admin CERCAR Requests" table, it gives my the shelf tally of the department for the said room - which is fine as I need for this information to be captured. BUT, I can't for the life of me, figure out how to get the grand total from the  "Admin CERCAR Requests" table to appear in the "Room requests" layout.

THIS is how I got to creating the calculation above (which sits in the "room details" table). I needed FM to find the number of shelves (separately) for each of the departments in "Admin CERCAR Requests" table and then add them up.   From here, I created another summary field that tallied these numbers to get to my grand total.

I am under no illusion that I have arrived at my destination in a convoluted way. As mentioned, both new fields in the "room details" table give me almost what I want (which is not good enough, though). I just don't know why it skips to the next room in the  "Admin CERCAR Requests" table once it encounters a dept other than the one it's trying to find. It just doesn't make sense to me.

Is there a simple way of getting what I need or must I restructure my tables?

Thanks.

1 2 Previous Next