Summing currency fields in base

classic Classic list List threaded Threaded
10 messages Options
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Summing currency fields in base

I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?

TIA
Adam.
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Fwd: [libreoffice-users] Summing currency fields in base

In field 3
=field1+field2
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, Oct 11, 2018 at 1:41 PM Adam Fenn <[hidden email]> wrote:

>
> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
>
> TIA
> Adam.
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

Thanks Wade, but where do I put that?

> On 11 Oct 2018, at 19:45, Wade Smart <[hidden email]> wrote:
>
> In field 3
> =field1+field2
> --
> Registered Linux User: #480675
> Registered Linux Machine: #408606
> Linux since June 2005
>
> On Thu, Oct 11, 2018 at 1:41 PM Adam Fenn <[hidden email]> wrote:
>>
>> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
>>
>> TIA
>> Adam.
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

If A1 is fee1
fee2 is B1
then in
c1 you type
=A1+B1

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, Oct 11, 2018 at 1:51 PM Adam Fenn <[hidden email]> wrote:

>
> Thanks Wade, but where do I put that?
>
> > On 11 Oct 2018, at 19:45, Wade Smart <[hidden email]> wrote:
> >
> > In field 3
> > =field1+field2
> > --
> > Registered Linux User: #480675
> > Registered Linux Machine: #408606
> > Linux since June 2005
> >
> > On Thu, Oct 11, 2018 at 1:41 PM Adam Fenn <[hidden email]> wrote:
> >>
> >> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
> >>
> >> TIA
> >> Adam.
> >> --
> >> To unsubscribe e-mail to: [hidden email]
> >> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> >> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> >> List archive: https://listarchives.libreoffice.org/global/users/
> >> Privacy Policy: https://www.documentfoundation.org/privacy
> >>
> >
> > --
> > To unsubscribe e-mail to: [hidden email]
> > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

On Thu, 11 Oct 2018 13:52:15 -0500
Wade Smart <[hidden email]> wrote:

> If A1 is fee1
> fee2 is B1
> then in
> c1 you type
> =A1+B1

You do realize the question is about base, not calc?

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Wade Smart Wade Smart
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

In reply to this post by Wade Smart
Ah, sorry. I cant see the whole subject line
on my phone. Though a quick search on
calculated fields shows you can:

http://sheepdogguides.com/fdb/fdb1calcf1.htm
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005
On Thu, Oct 11, 2018 at 2:00 PM Adam Fenn <[hidden email]> wrote:

>
> I'm not using Calc, I'm making a database using base. If you use a drop down field there is a place in the properties to add sql but not in a currency field. I'm wondering how you do it.
>
> > On 11 Oct 2018, at 19:52, Wade Smart <[hidden email]> wrote:
> >
> > If A1 is fee1
> > fee2 is B1
> > then in
> > c1 you type
> > =A1+B1
> >
> > --
> > Registered Linux User: #480675
> > Registered Linux Machine: #408606
> > Linux since June 2005
> >
> > On Thu, Oct 11, 2018 at 1:51 PM Adam Fenn <[hidden email]> wrote:
> >>
> >> Thanks Wade, but where do I put that?
> >>
> >>> On 11 Oct 2018, at 19:45, Wade Smart <[hidden email]> wrote:
> >>>
> >>> In field 3
> >>> =field1+field2
> >>> --
> >>> Registered Linux User: #480675
> >>> Registered Linux Machine: #408606
> >>> Linux since June 2005
> >>>
> >>> On Thu, Oct 11, 2018 at 1:41 PM Adam Fenn <[hidden email]> wrote:
> >>>>
> >>>> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
> >>>>
> >>>> TIA
> >>>> Adam.
> >>>> --
> >>>> To unsubscribe e-mail to: [hidden email]
> >>>> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> >>>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> >>>> List archive: https://listarchives.libreoffice.org/global/users/
> >>>> Privacy Policy: https://www.documentfoundation.org/privacy
> >>>>
> >>>
> >>> --
> >>> To unsubscribe e-mail to: [hidden email]
> >>> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> >>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> >>> List archive: https://listarchives.libreoffice.org/global/users/
> >>> Privacy Policy: https://www.documentfoundation.org/privacy
> >>
> >>
> >> --
> >> To unsubscribe e-mail to: [hidden email]
> >> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> >> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> >> List archive: https://listarchives.libreoffice.org/global/users/
> >> Privacy Policy: https://www.documentfoundation.org/privacy
> >>
> >
> > --
> > To unsubscribe e-mail to: [hidden email]
> > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Girvin Herr-5 Girvin Herr-5
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

In reply to this post by Adam Fenn
Adam,

I am not sure where you are wanting this total. There may be other
methods to do this, but I prefer to do this calculation in a query. I
use separate queries for reports and forms so a change in one does not
affect the other. First, you need to create a query, if you have never
created a query, then there is a learning curve you need to go through
to create one. You could start by using the wizard to create the query
and then save it and quit it. Then you can edit it in "Design Mode".
Once in the Design mode, you can verify that your table is in the upper
frame and your table field names are in the lower frame with the
"Visible" block checked. This will get you what you have now and you can
build on it. You need to add a field to the bottom frame with your
formula in the "Field" block. For your example, it would look like 
"(fee1+fee2)" (no quotes). You probably don't need the parens either,
but it is safe. Then, in the  "Alias" block of this new column, you need
to add your new field name. In your example, it would be "Total" , again
no quotes. Make sure the "Visible" block is checked or the total result
will not be passed out of the query. If you already have a "Total" field
in your table, then you need to have another, unique, name.

Then you need to have your form attach itself to the query rather than
the table. You do this by opening the form in "Edit" mode and selecting
the "Form" icon. At this time, you should have a popup called "Form
Properties". Select the "Data" tab and in the "Content type" list select
"Query". Then select your new query in the "Content" list. Then you can
add the new "Total" field to your form. Doing so in a report is similar.

This is just a short description. You should be familiar with LO queries
to do this. All of this may be documented well by now. So, read up on it
if you want to create a query.

Note: One should never have calculated fields in a database. It wastes
space.

Note also that I am using Mariadb (MySQL). If you are using the LO
bundled database server, then this may be different.

HTH.

Girvin



On 10/11/18 11:39 AM, Adam Fenn wrote:
> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
>
> TIA
> Adam.

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

In reply to this post by Adam Fenn
Le 11/10/2018 à 20:39, Adam Fenn a écrit :

Adam,

From an DB administrator/designer's perspective, calculated field values
are not generally stored in the database if they don't need to be. A
view might solve your problem in this case, as a view is a kind of fixed
query. It should be possible to create a view that calculates the sum of
your two fields and then show that in a column of the view as the result
or total.

Alternatively, you could define a query that would calculate the total
value for you, but it wouldn't insert that value into a field
specifically designed to store the value (display only).

If you do want to insert the actual total value into a field so that it
is stored in the database table, then you would probably need to define
a trigger (or stored procedure) that performs the calculation each time
a change is made to either Fee1 or Fee2, or upon some other condition.

You don't say, however, which kind of db engine you are using and
triggers are not available for all database sources supported by LOBase
(for example Calc, CSV, text tables, Dbase).

If you didn't want to perform this automatic calculation and storage at
the database engine level, but at a user interface level, then you could
use a Basic macro to effect the calculation, insert the total value and
refresh the table/form after insertion.


Alex






--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

In reply to this post by Girvin Herr-5

> On 11 Oct 2018, at 23:39, Girvin Herr <[hidden email]> wrote:
>
> I am not sure where you are wanting this total.

I want it in the same form as fee1 and fee2.

> First, you need to create a query,

I'm familiar with this.

> Then you need to have your form attach itself to the query rather than the table. You do this by opening the form in "Edit" mode and selecting the "Form" icon. At this time, you should have a popup called "Form Properties". Select the "Data" tab and in the "Content type" list select "Query". Then select your new query in the "Content" list. Then you can add the new "Total" field to your form.

When I try to add the field using the Add Field icon LO crashes without fail. Is there a way around this?

Thank you for your help Girvin.

> On 10/11/18 11:39 AM, Adam Fenn wrote:
>> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
>>
>> TIA
>> Adam.
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Dan Lewis Dan Lewis
Reply | Threaded
Open this post in threaded view
|

Re: Summing currency fields in base

Perhaps you need to begin over as far as you fields are concerned. First
of all, you only need two fields in the table: Fee1 and Fee2. You also
need a primary key which is listed first. Fee1 and fee2 need to be given
the properties you want for them. The primary key has to have "entry
required" selected. I prefer it to also have this property: INTEGER.
Save the table. When I did this, I named the table, Currency, and the
primary key, ID.

Second, create a query in SQL View. Use this SQL statement:

SELECT "ID", "Fee1", "Fee2", "Fee1" + "Fee2" AS "Total" FROM "Currency"

Save the query giving it a name other than Currency. (You can not have a
table and query with the same name.)

Running the query, the ID field has <AutoField>. You can not enter
values for Fee1 and Fee2. Typing <ENTER> after entering values for Fee1
and Fee2 will place the sum in the Total column.

Now you can either use the Form wizard to create a form from the query
or use Girvin's suggestion for connecting your present form to the query.

Dan

On 10/13/2018 07:07 AM, Adam Fenn wrote:

>> On 11 Oct 2018, at 23:39, Girvin Herr <[hidden email]> wrote:
>>
>> I am not sure where you are wanting this total.
> I want it in the same form as fee1 and fee2.
>
>> First, you need to create a query,
> I'm familiar with this.
>
>> Then you need to have your form attach itself to the query rather than the table. You do this by opening the form in "Edit" mode and selecting the "Form" icon. At this time, you should have a popup called "Form Properties". Select the "Data" tab and in the "Content type" list select "Query". Then select your new query in the "Content" list. Then you can add the new "Total" field to your form.
> When I try to add the field using the Add Field icon LO crashes without fail. Is there a way around this?
>
> Thank you for your help Girvin.
>
>> On 10/11/18 11:39 AM, Adam Fenn wrote:
>>> I have three currency fields - 'Fee1', 'Fee2' and 'Total'.  How do I add fee1 and fee2 together and put the result automatically into the Total field?
>>>
>>> TIA
>>> Adam.
>> --
>> To unsubscribe e-mail to: [hidden email]
>> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy