I Am Having Trouble Adding & Subtracting Calculation Fields
Hi! I have what I think is an easy question, but I can't get it to work.
I am in the real estate business. I buy houses fix them & resell them.
I have 1 workspace with 3 different apps.
App 1 is just general information about the property such as property address, # of bedrooms, etc. This app works fine.
App 2 - Labor (This is where I list all the costs that each contractor charges such as plumbing, electrical, etc.) Below is a screenshot of part of App 2. App 2 also has a Relationship field to App 1.
App 2 above also has a Calculation field at the bottom of each contractor category, which totals up the costs for that contractor. So for example, I have a Calculation field after plumbing named Total Amount For Plumbing, one after electrical called Total Amount For Electrical & so on. That Calculation tells me how much I owe each individual contractor.
Also in App 2 I have another Calculation field that totals up all of the different Calculation fields combined & gives me a grand total for all of the different contractors. Below is what that grand total Calculation looks like.
As best as I can tell all of the Calculations in App 2 above work as they should.
App 3 - Payments This is where I want to list payments made to EACH different contractor as I make those payments & keep a balance of how much I still owe for each contractor. Then I want to have a 2nd Calculation that is separate to keep track of the TOTAL balance still owed for ALL contractors combined. In other words, I want to calculate how much I still owe to each separate individual contractor as each payment is made to them & then I want to have a separate calculation that shows the total of how much I still owe for ALL of the contractors combined.
Below is a snapshot of part of App 3. I have a Relationship field in here to App 1 & App 2.
App 3 is where the problem is. Below is the Calculation I tried to use for Plumbing. When I try to pull over the Total Amount For Plumbing field from App 2, it will not calculate that field, even though I selected it from the drop down list.
The reports show that the rest of the calculation did just fine, but it will not include the @Sum of Total Amount For Plumbing field in the calculation.
To summarize:
How would you set up the calculation for EACH individual contractor to show the total balance that I owe minus the amount that I have paid each one of them so far?
How would you set up the 2nd calculation so that it gives me the TOTAL that I owe to ALL contractors combined minus the amount that I have paid them so far?
Does anybody know what formulas I need to use to accomplish these Calculations?
Thank you so much for your time. This is a very nice forum by the way.
-
Jean-Claude
Hi! Thanks for answering. No I only have one Relationship field from App 2 (Labor) to App 3 (Payments). That Relationship field is in App 3 (Payments). In both App 2 & App 3, I also have a Relationship field to App 1, just so that I can select the proper record from the list.
Also, just as a test, I created 2 new apps that were very basic just to see if I did something wrong in my original apps, but I still cannot get the Calculation field from the 1st app to calculate in the 2nd app, even though I selected it from the drop down list. It will do the rest of the calculation just fine, but it will not include the calculation from that 1st app.
I even tried calculating 2 calculation fields together, instead of mixing Calculation & money fields but it still will not bring over the amount from that 1st app.
When I am in the 2nd app, I also select the proper record that I want from the Relationship field. Should I not select any record from the Relationship field at all?
Is there a maximum number of fields that can be involved in a Calculation?
This whole thing is getting very frustrating.
-
Jean-Claude,
UPDATE: I have it working now. Thank you very much for taking the time to answer this post. I was trying what you said about the incoming/outgoing relationship direction. Sure enough, that was exactly what the problem was. Originally I only had one relationship in App 3 from App 2. I added a relationship from App 3 back to App 2, so that both apps now had relationships to each other. I assumed App 3 would be incoming, from App 2, but for some reason it is considered outgoing. Once I changed it from incoming to outgoing from the drop down list in the the calculation field everything started working like it should.
I am thinking that I originally must have picked the wrong one, then when I made different changes, it just kept what I had already picked which was incoming. I don't know. But anyway, it's working now. So Thank you again for taking the time to answer.
So now my question is when do you use incoming & when do you use outgoing?
Also, is it necessary for each app to have a relationship to the other app, or would just one relationship be good enough?
-
Hi Vic,
So now my question is when do you use incoming & when do you use outgoing?
There is no rule. It depends on how you need it and how it fits to your workflow and flow of data. There is only one condition which can force you to do the relation the other direction than neeed: A relationship field can have only max. 299 related (outgoing) items. Incoming relations are unlimited.
Also, is it necessary for each app to have a relationship to the other app, or would just one relationship be good enough?
In my opinion vice versa relations should be the exceptional case (and shouldl be avoided), just one relationship should be the normal case.
Rainer
Please sign in to leave a comment.
Comments
6 comments