Discussions

Expand all | Collapse all

Need help adding field values.

  • 1.  Need help adding field values.

    Posted 10-25-2017 17:28
    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.


  • 2.  RE: Need help adding field values.

    Posted 10-25-2017 17:57

    Go into the properties for the field Billed Amount. In the section Numeric Field Options/Totals and Averages, check the totals box.

    You can set your report to group by year on Billed Amount. There will be a total for Billed Amount at the end of each year's group in the report.

    hope this helps.



  • 3.  RE: Need help adding field values.

    Posted 10-27-2017 20:59
    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.


  • 4.  RE: Need help adding field values.

    Posted 10-29-2017 14:45

    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.



  • 5.  RE: Need help adding field values.

    Posted 10-31-2017 15:39
    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.


  • 6.  RE: Need help adding field values.

    Posted 11-02-2017 22:56
    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!


  • 7.  RE: Need help adding field values.

    Posted 11-03-2017 13:51

    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.



  • 8.  RE: Need help adding field values.

    Posted 11-08-2017 16:28
    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:

    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1670445/RackMultipart20171108-5778-thh84p-2017-11-08_11-27-19_inline.png?1510158470">


  • 9.  RE: Need help adding field values.

    Posted 11-08-2017 16:45

    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.




  • 10.  RE: Need help adding field values.

    Posted 11-08-2017 16:51


  • 11.  RE: Need help adding field values.

    Posted 11-08-2017 16:52


  • 12.  RE: Need help adding field values.

    Posted 11-08-2017 16:54
    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.


  • 13.  RE: Need help adding field values.

    Posted 11-08-2017 16:54
    Now, create some Invoicing records. The [Related Record] field has to be resident in the Invoicing form.


  • 14.  RE: Need help adding field values.

    Posted 11-08-2017 17:01
    Alrighty!  I see it working - as well as my issue.  It does add the total values but only if / when the amounts are saved.  It doesn't "refresh" to add all historic invoices.  So if I open & save each I should be OK, yes?


  • 15.  RE: Need help adding field values.

    Posted 11-08-2017 17:02
    Exactly, the form rule was added later. so yes, you're correct that you'll have to resave them, but do that in Grid edit mode so you can so all of them in seconds.