Newbie with Base: how to modify the database so a report only applies to the last data I entered?

English
,
#1

I am using LO Base for invoices. I created my template following the instructions of these videos, but I’m facing 3 problems:

  1. The number of the first invoice is 1 instead of 2122001, which is the number I assigned to it when I entered all the data in the forms.
  2. I’m getting several invoices at the same time instead of just the ones that are not exported in PDF.
  3. My functions [Subtotal] ([Amount]*[Price]), [Tax] and [SubtotalWithTax] are being accumulated from invoice to invoice so the result are correct for the same invoice but not for the others because it sums up the amount of the invoice and precious ones.

What can I do?

#2

Hello,

Kind of conflicting. Main question states:

how to modify the database so a report only applies to the last data I entered?

then:

and what is the problem?

Where did PDF and ones come from? Question stated to print last entered!

For #3 you should provide a sample. Easier to figure out where the problem might be.

Sample and more clarity is needed.

1 Like
#4

Hey, thanks for replying!

how to modify the database so a report only applies to the last data I entered?

I know, I wasn’t sure how to explain myself and what did I wanted. Sorry for the confusion. When I execute the report (using the Report builder) it shows all the 3 invoices I entered on the form. I wanted some trick so it only shows the ones I want them to show (I’m right now thinking on adding maybe a boolean “printed”/“not printed”, maybe I can make it use the data from forms with “not printed”. By “printed” I mean exported in PDF after the report is executed.

first invoice is 1 instead of 2122001, which is the number I assigned to it when I entered all the data

The thing is that the number of the invoice (that I entered in the form) is 2122001, but when I execute the report, it appears as just 1. Only noticed it with the first invoice, not the others.

Ok, I think I modified it enough to make it kinda anonymous, sorry for the waiting.
Example of invoices for askLO.odb (45.2 KB) (EDIT: in this version, the first invoice has the number 2122001 for some reason lol, I’ll modify the original to see if I can fix it)

#6

Hello,

If you are looking to print multiple (but not all), you can add a field & use that to determine whether to print or not.
.
Your note on Invoice # is not what I see. They look fine to me.
.
As for the totals, your functions are in the wrong place. You have them under Report and they belong under Group. With that change they work.

1 Like
#7

Dear stranger, you have no idea how much this did help me :heart_eyes:

BTW, I can’t find how to add the other variable once all the parts of the database are created

#8

Would imagine this is to be added to the Facturas table. Right mouse click th table name and select Edit. Then add the field.

#9

I did this already but doesn’t seem to influence the report once I said what data I wanted when creating it

#10

It needs to become part of the SQL. You need to retrieve only records which have the field set.

Add it in the table, set in a form (or maybe table maintenance) and include only these noted invoices in the query.

#11

I’ve been trying to do it, but I’m not able to do it.

I added a boolean in Tareas (Tasks) and Facturas (Invoices) and made a new query which includes it. In the report, I changed it to use this query as source with a filter but I’m not sure what am I missing. Maybe I’m just doing something wrong, idk.

#12

There may be a better way. Although I can easily (for me) do this with macros, it may be overwhelming for you.
.
Possibly print one at a time. This can be done with a parameter added to the SQL.
.
Using the sample you posted, add this:

 AND "Facturas"."IDFactura" = :Invoice

.
to the end of query Consulta1. When you run the report it will ask for the invoice number to print.

#15

I found out that the filter in the query must be “true” or “false”, instead of “0” or “1” lol. For booleans, oc

#3

menu:Tools>SQL…

UPDATE "table" SET "column"="column" +2122000

menu:View>Refresh Tables
New query in SQL view:

SELECT * FROM "table" WHERE "column"=(MAX("column"))

and assign the report to this query.

#5

I’m guessing this adds “2122000” to every invoice?

SELECT * FROM "table" WHERE "column"=(MAX("column"))

What does it do? I don’t want to enter any code without knowing what am I doing, hope you understand :sweat_smile:

#13

Any source on relational databases will tell you. This is the same structured query language (SQL) since decades.
The first statement updates the table named “table” and adds 212200 to the column named “column”.
The second statement is a SELECT query (menu:Insert>Query>SQL…) which selects all columns from the table named “table” where the column named “column” equals the maximum of that column. If “column” is enforced to be unique (e.g. a primary key), it will always return a single row.

#14

I see, thanks! :slight_smile:

#16

I found out that the filter for booleans in the query must be “TRUE” or “FALSE”, instead of “0” or “1” lol.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.