Need help adding field values.
Hello All. I'm trying to add revenue values (Field=Billed Amount ) to determine a Year-To-Date value. So I need to add all fields in the column related to this year.
Forum Discussion
Hi Chris. Thanks so much for your reply. I've done the above & see the total, but I need that total to appear in a field so I can reference/access it elsewhere.
Hi Bill,
Most of the solutions I use are out of the box Quick Base. So, I'll provide a Quick Base solution that does not involve scripting.
For the table where [Billed Amount] resides, you will create a parent table that will summarize the total of [Billed Amount].
- Create a parent table to the table that contains [Billed Amount].
Go to the relationship interface where you see the parent on the left and its child on the right.
On the parent side create a summary field that summarizes the total or sum of [Billed Amount].
You can now pass this summary value back to the child table as a lookup field. It will appear in every child record of course so you may want to add comments to that field so you'll know what it's for, for future reference.
Let us know if you run into any difficulties.
- Thank you again for your help. I'm starting to pull my hair out...
I've done (I believe) as you instructed - I see the field in the appropriate table but there is no data in it. Relationship screen cap attached. - Not sure if this is an issue, but above you'll notice that the "related record" type is Numeric, but the field needed is Currency. I've tried re-creating this to change it but have not been successful.
Appreciate the help! Bill
I think I know what's happening. The Calculated Data table needs only one record. Just add one record, it does not have to have data of its own. Go ahead and make one parent record in Calculated Data.
Now...
When you save your details records in Monthly Invoices, you will use your form rules to create the relationship between the Monthly invoice Records and the one "Calculated Data" Record.
In the form rules area for the Monthly Invoices form, make a new rule::
When the record is saved change Related Record to 1. << This is assuming that the one Calculated Data record has a Record ID# of 1. If the Record ID is not 1, then change your form rule to match the Related Record to whatever that Calculated Data record's ID number is.
Let us know how it goes.
- Good morning,
My continued issue appears to stem from having no value appear in the calculated totaled billed amount field. So in the parent window, values are not appearing that represent the calculated amount: The [Sum billed amount] summary field on the left has be to modified to be the sum or total of its child records' [Invoice amount] or what that field is in the Monthly Invoices table to sum up.
Click on [Summed Billed Amount] on the left.
modify its properties to summarize "Totals" or "sum" of the [Invoice Amount] in the child table.
IN the child table's form, set a form rule that makes [Related Record] equal to 1 when the child record is saved.
Let's stop there and let you catch up. Let us know how it goes.
- Excellent! Now when you have this saved, go back to the relationship view and move the [Sum Billed Amount] summary field to the Monthly Invoices table as a lookup field. This does NOT remove it from the parent, but just makes an instance of it in the child. Note it will be in all child records, so you will manipulate it in reports.
- Now, create some Invoicing records. The [Related Record] field has to be resident in the Invoicing form.