## Recommended Posts

Good Afternoon.

I was wondering if anyone could possible assist me.

I wondered how to total field values if the field was a certain value.  Like if I had a list of items a1 a1 b1 c1 a1 how can you only add the a value then the b value etc etc.

Hope this is quite clear to understand.

Thank you very much for any help or advice.

##### Share on other sites
5 minutes ago, Faldo said:

Hope this is quite clear to understand.

I am afraid not.

##### Share on other sites

Sorry

Il try again.  😤  Im trying to sum individual values of individual fields.  Like sum them if they equal the criteria.

If I had a list of items and prices with items the same name but different prices sum them up if the were say red.  And another sum if they were Blue and so on.

Phew hard going.   Is that any better. 😣

##### Share on other sites
2 hours ago, Faldo said:

If I had a list of items and prices

Such list should be stored as individual records, not individual fields.Then you can simply find the records you want to summarize, sort them by the Name field and show them in a list layout with a sub-summary by Name part. A summary field defined as Total of Price, and placed in the sub-summary part, will show the total of each group. If you like, you can delete the body part so that only sub-totals are shown.

Alternatively, you could have a parent table of Names where each name is a unique record. Then you can summarize the matching records in the Items table using either a summary field (defined in the Items table) or a calculation field using the Sum() function (defined in the Names table). Keep in mind that this will include all related records, without the possibility of performing a find.

Finally, you also have the option of using the ExecuteSQL() function, with a query like:

```SELECT Name, SUM (Price)
FROM Items
GROUP BY Name```

2 hours ago, Faldo said:

Phew hard going.

Your question has 63 words. My answer above has 182. It should be the other way around.

##### Share on other sites
3 minutes ago, comment said:

Such list should be stored as individual records, not individual fields.Then you can simply find the records you want to summarize, sort them by the Name field and show them in a list layout with a sub-summary by Name part. A summary field defined as Total of Price, and placed in the sub-summary part, will show the total of each group. If you like, you can delete the body part so that only sub-totals are shown.

Alternatively, you could have a parent table of Names where each name is a unique record. Then you can summarize the matching records in the Items table using either a summary field (defined in the Items table) or a calculation field using the Sum() function (defined in the Names table). Keep in mind that this will include all related records, without the possibility of performing a find.

Finally, you also have the option of using the ExecuteSQL() function, with a query like:

```
SELECT Name, SUM (Price)
FROM Items
GROUP BY Name```

Your question has 63 words. My answer above has 182. It should be the other way around.

Hi thanks a million will have a go.  You have more words as you have more wisdom and knowledge.  Thanks again. 🙄😁

##### Share on other sites

Hi sorry just one more thing if you can.  How would you go about defining if the Price was a credit or a debit?  Would you use 2 seperate fields one dredit and one debit.  I had a dropdown which you can choose money in or money out which didnt seem to work.  Thanks for your help and patience.

##### Share on other sites
Posted (edited)

It depends on how you want to work with it and/or what you want eventually to do with it. The simplest way is to enter debit amounts as negative numbers. Ledger-type solutions will have separate fields for credit and debit - but then a calculation field will return credit as positive and debit as negative, so that a single summary field can be used for the balance.

2 hours ago, Faldo said:

I had a dropdown which you can choose money in or money out which didnt seem to work.

That could work, too - provided you also had a calculation field like the one descried above, that would return the amount as negative if you selected "money out".

Edited by comment
##### Share on other sites

Brilliant thats great thanks.

## Create an account

Register a new account

• ### Similar Content

• I am using Filemaker Server 18 on Windows Server 2012 R2
Been using it for years with no issues
Currently when I log in to the console it is very sluggish.
When I get to the Dashboard it shows No databases, then it auto refreshes and the database list appears.
Within 15 seconds of scrolling the database list to open files the screen refreshes. This situations is happening over and over in a loop.
Any Thoughts on what is causing this issue?

• I get an error 3 when using a script to Export Records via WebDirect. Using FileMaker Server 18 and have tried both Safari and Chrome both with same results. I have tried using the temporary path, desktop path, and documents path. I have tried using with the automatically open and not. I have tried writing a tab delimited and comma delimited file. Does anyone have ideas I haven't yet tried?

• Hi All

I am hoping some one can help!
I have a table called TPM, in that table is a field "Next Due Date" Which is auto generated by creating a new record. You input the date you did the service and then it adds 3 months to that date and that appears in the Next Due Date Field.. All working...

What I am trying to do is when the database loads it runs a script to see if there are any outstanding TPM Services to do...
Here is the script
Show Customer Dialog
Message: If ( TPM::TPM Next Due Date < Get (CurrentDate ) ; "You have outstanding TPM services" ; "You have no outstanding services")
I thought it would be that simple. So I created a TPM Due date Less that Current date and ran the script...."You have outstanding TPM Services"...GREAT! Then I deleted that record and ran the script again...""You have outstanding TPM Services" Oh!!! That isnt true!

• Hi,
Can someone please explain why Leftwords function is not properly working when importing an excell sheet.
For example the name John Doe LeftWords ( Client ; 1 ) should show "John" only.
When typing John Doe it works fine but not when importing ????
Kind regards
Rudy
• By Pio Soto
Hi,
My issue is that my customer layout has a script trigger on LayOutEnter that sorts my customers portal list by "Active" customers,
but when I do a go to related record from another table say from quotes or any other the customer layout does not load that customer
or show my related customer because the OnLayoutEnter script trigger changes it, I would like it to go to the related customer and also sort it by "Active"
on the list.
Hope someone can help me with this.
Thanks,
Pio

×
×
• Create New...