Jump to content

Adding up values depending on its type


This topic is 2358 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi, 

I need some help with a little problem that I have found repeatedly. I attach one image of one of the cases

In the first table, I have a ProyectoIdFk (Proyecto Foreign key, to relate the values in this table to another one, the projects table), an Id field which is a unique number for each row (or specifically, an addition. A value added to the initial value of the project),  TipoAdicion (Addition Type) that could be either Obra (Construction) or Interventoría (Oversight), ValorAdicion with the value of each addition and c_ValorAdicionesOBRA (Calculation field to add up all the additions defined as Obra) which isn't working properly yet and therefore I need your help. 

5695badff15f6_Capturadepantalla2016-01-1

 

I would like to know how to sum, for each ProyectoIdFk the additions' values marked as "Obra" as shown in the table below I made in Excel to make understanding easier.

.Result_wanted.png.7e3b169f3a28c474a17b16

For ProyectoIdFk = 1 (Repeated 4 times because the project has 4 additions) , I'd like the field c_ValorAdicionesOBRA sums the 3 additions defined as Obra to get a total of 40'000.000. The same for ProyectoIdFk = 2.

My intuition says I should include the c_ValorAdicionesOBRA in the projects table (so it can be related to each project), but when I do this, it keeps adding up every addition (Obra + Interventoria) and I just want the Obra ones. I guess I'm trying to mimic the sum.if from Excel but I already realized FM works differently... :/ 

Thanks in advance for your help. 

 

Link to comment
Share on other sites

The simple solution would be to find only items whose type is "Obra", sort them by ProyectoIdFk, and place a summary field defined as Total of [ ValorAdicion ] in a sub-summary (when sorted by ProyectoIdFk) part.

If you want to keep the other types in the found set, you will need to also sort by TipoAdicion and add a corresponding sub-summary part. If you like, you can use conditional formatting to "hide" the other sub-summary values.

There is no SUMIF() function in Filemaker, and to mimic it perfectly you would have to add both a calculation field = If ( TipoAdicion = "Obra" ; ValorAdicion ) and a summary field to total it - which IMHO is too much effort for the expected return.

 

Edited by comment
Link to comment
Share on other sites

  • Newbies

Thanks for your quick reply. 

My next question would be, is it possible to "save" the sub-summary values in any field so I can use it in a calculation in another table? In other words, lets take the same example shown in the pictures. I do what you suggested me to do and I got for ProyectoIdFk = 1 a value = to 40'000.000 in the fiel "Total" (Sorted in a sub-summary by Proyecto IdFk and so on) and I want to "call it" from the "Proyectos" table so I can add it up to another value, let's say the "Total cost" of the project. Would it be possible?

Link to comment
Share on other sites

Anything is possible - but it's not possible in the way you describe. The method I suggested summarizes a given found set in the Additions (?) table, and the results cannot be "called" from another table. You would either need to transfer the values using a script, or use a completely different method to summarize the related Addition records from the context of the Projects table.

You can, however, get the "Total cost" of the parent project and add it to the summary (or sub-summary) values produced by the suggested report on Additions. OTOH, such report would not necessarily include all projects - only those that have additions in the given found set.

I suggest you explain in more detail what's your ultimate purpose here.

Link to comment
Share on other sites

This topic is 2358 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.