1 Topic by joshuA (edited by joshuA 2021-09-05 13:40:57)

  • Registered: 2019-12-24
  • Posts: 169

Topic: [SOLVED] Form Reporting & Footer fields

Greetings!


I could use a few tips with this report.


A) I'm trying to re-create an existing form. So far, I have found two options:


  1) paste a copy of the original form in the background of a report.
    I don't prefer this one because I can't determine print margins to line up with the data fields. (Very tedious)


  2) populate fields with (a pre-defined amount) of NULL values.
    However, this is a lot of overhead in the database.
    And- I was unable to get this working with my example.


This may be obvious- but the reason for this, is because the normal form can be hand-written. The printed report is intended to replicate this form which will allow for further hand-written notes.


B) Also, I'm unable to reference fields in any of the footers. To indicate who created the entry before signing it. (I have attempted this in the example)


Aside note, I ran across an old post from derek back in 2017 (I was unable to locate it when I posted this), but he was asking the exact same question. Back then he was asking if was a bug, and there was no reply after. So it makes me wonder if it may be possible now.

Post's attachments

Attachment icon report-form.zip 328.64 kb, 325 downloads since 2021-09-01 

"Energy and persistence conquer all things."

2 Reply by sparrow (edited by sparrow 2021-09-01 13:03:11)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Form Reporting & Footer fields

http://myvisualdatabase.com/forum/viewt … 7643#p7643

kunar80
На сколько я знаю, да, в Footer нельзя вывести данные из базы, можно только расчеты, или с помощью скрипта.

kunar80
As far as I knows, you cannot display data from the database in Footer, you can only use calculations, or using a script.


Maybe you can find a solution here

http://myvisualdatabase.com/forum/viewtopic.php?id=5919

  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Form Reporting & Footer fields

Thanks for finding that post sparrow. That one is also an old thread from 2015. I was hoping that there might be a way to do it now, but it seems that footers are for calculations only.

As for the other question, I will resort to my 2nd method for now. I'm hopeful that someone has a different technique that they will share.

"Energy and persistence conquer all things."
  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Form Reporting & Footer fields

it is possible in more detail about the 2nd method with an example?
what is needed ?

5 Reply by derek (edited by derek 2021-09-01 21:32:10)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,761

Re: [SOLVED] Form Reporting & Footer fields

Hi Joshua, Sparrow,
If I understand your question, try doing it like this (see attachment and screenshot).
The key is to use 'min' as an aggregate function (although I've never seen it documented anywhere).
Hope this helps,
Derek.

Post's attachments

Attachment icon DB fields in FastReport Footers.zip 1.2 mb, 341 downloads since 2021-09-01 

6 Reply by joshuA (edited by joshuA 2021-09-01 23:02:27)

  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Form Reporting & Footer fields

Okay, I was able to find and create solutions to both of my questions. I feel certain that there is a better way to do the blank-line reporting without creating empty records in the database.
.
Derek, I was able to get the signature field in the footer using some SQL. I stumbled around and noticed the Code section in FastReport. I created a variable from there holding the value of the query. But I do prefer the way you mentioned much better. That's exactly what I was looking for.
.
Sparrow, as for more details have a look at the attached example. It demonstrates the reports that I'm after. Namely, a form that matches the format of a company standard form with a set amount of lines. Regardless of the data, or lack thereof.
.
Again, my approach populates empty rows into the database in order to achieve this. It is certainly not the ideal solution, but until I can find another way, this will get me by for now. Hopefully someone else can make use of the example too.
.
As always, I appreciate the help and suggestions!

Post's attachments

Attachment icon standard-form-report.zip 333.99 kb, 315 downloads since 2021-09-02 

"Energy and persistence conquer all things."

7 Reply by sparrow (edited by sparrow 2021-09-02 12:31:43)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Form Reporting & Footer fields

Hi Derek,Joshua.

Quick solution to not insert null row data after records.
SQLite does not support variables and we will not create temporary tables in memory.
Create a separate table. For example "tenlines". With one column "num". Fill it in with numbers from 1 to 10 or any number 1 only all 10 lines.
You can enter data into the table in the script once if it is not filled.

Edit Report (SQL). We will UNION with data and limit the output. LIMIT 10. 

SELECT
    orders.id as "orders.id",
    orders.customerName as "orders.customerName",
    orders.orderDate as "orders.orderDate",   
    orders.salesRep as "orders.salesRep", 
    orderLines.qty as "orderLines.qty", 
    orderLines.number as "orderLines.number",
    orderLines.description as "orderLines.description",
    orderLines.price as "orderLines.price",
    NULL
FROM                                           
    orders
JOIN orderLines
    ON orderLines.id_orders=orders.id
WHERE
    orders.id=$id
UNION ALL
SELECT $id as "orders.id",
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
num
FROM tenlines
ORDER BY 9 -- sort by 9 columns
LIMIT 10

OR without create table
It is irrational if the number of lines will be more. )

SELECT
    orders.id as "orders.id",
    orders.customerName as "orders.customerName",
    orders.orderDate as "orders.orderDate",   
    orders.salesRep as "orders.salesRep", 
    orderLines.qty as "orderLines.qty", 
    orderLines.number as "orderLines.number",
    orderLines.description as "orderLines.description",
    orderLines.price as "orderLines.price",
    null
FROM                                           
    orders
JOIN orderLines
    ON orderLines.id_orders=orders.id
WHERE
    orders.id=$id
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 5 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 6 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 7 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 8 as num
UNION ALL
SELECT $id as "orders.id", NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9 as num 
ORDER BY 9 -- sort by 9 columns 
LIMIT 10

And remove rows with NULL or create new table "orderLines". Remove procedure add NULL from script.
If you have any difficulties, I'll post a modified version.
I'll watch the report later.

8 Reply by derek (edited by derek 2021-09-02 17:13:39)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,761

Re: [SOLVED] Form Reporting & Footer fields

Hi Both,
I think the FastReport option ('completetoNrows' in a child band) would be the answer. 
(https://www.fast-report.com/documentati … ybands.htm)
That's the good news!! 
The bad news is that I don't think it's available in the version of FastReport that is shipped with MVD (I had a very quick look and couldn't see how to get at it).
In its absence, the only suggestion I might make would be to 'flatten' your structure and have a single 'orders' table with 10 repeats of the data. 
It's not 'text book' but it would get around most of the problems (blank rows, pre-setting column widths and the report layout) and would cut the script (in MVD and in FastReport) down to a minimum.
If you ever have orders with more than 10 lines, you can take an 'order no/suffix' approach where the order no' + suffix identifies a discrete order and order no' (without suffix) groups the related orders together for reporting. 
But maybe the >10 lines scenario never happens?
Derek.

9 Reply by sparrow (edited by sparrow 2021-09-02 17:26:49)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Form Reporting & Footer fields

Hi Derek.

As I understand it, the Table in FastReport should be of fixed length. Let's say 10 lines and no matter how many of them are filled. All others are empty.
Therefore, the solution above is proposed without unnecessary entries with NULL in the data table.
FastReport hasn't looked at it yet.

joshuA wrote:

... a form that matches the format of a company standard form with a set amount of lines....

I also had a question and if > 10 or another amount. Probably the company has its own answer.

DriveSoft link to FastReport 4 version documentation.

10 Reply by ehwagner (edited by ehwagner 2021-09-02 17:35:50)

  • Registered: 2015-11-13
  • Posts: 652

Re: [SOLVED] Form Reporting & Footer fields

Hey Gang,
Let me put my two cents worth into this. I have a way where it does not require any extraneous empty data rows.and I think it will take care of the requirement. In the attached project I am using an overlay which places the order lines on the empty page. Then to get the total underneath the order grid lines I had to expand the Page Footer section and force place the total in its proper location. This works but if there are more than 10 lines I am not sure what it will look like. I did not test that part. There probably should be some script to limit the data lines from going over 10, unless you expand the grid.


Also, it takes a little bit of effort to create/manipulate the grid lines to matchup with the data. If you need to move the entire grid, just select all the grid lines and move it or if you need to move individual lines, just select that particular line and move it. If you add more grid lines then you will also need to expand the overlay and reduce the footer section and move the total to matchup with the grid cell for the total. Once everything is in place and the max rows on an order is defined, then this design should not need to be modified on an ongoing basis.


PS. The FastReport designer window is not large enough to show every component on the page due to the overlay, but all the components are there  in the footer. In a real project all the necessary footer info should be placed before expanding and placing the total in the expanded area. Expanding the footer section makes the bottom components invisible in the designer window, but they are there.

Post's attachments

Attachment icon standard-form-report Revised.zip 344.8 kb, 311 downloads since 2021-09-02 

11 Reply by sparrow (edited by sparrow 2021-09-02 19:02:14)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Form Reporting & Footer fields

Good decision ehwagner


Small correction for more than 10 records in one Order.
Continued printing on the next page. Again 10 per sheet, etc.
Order # 10 contains more than 20 test records.

Post's attachments

Attachment icon standard-form-report Rev 1.zip 342.28 kb, 343 downloads since 2021-09-02 

12 Reply by joshuA (edited by joshuA 2021-09-02 21:29:50)

  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Form Reporting & Footer fields

Hello folks!
.
I'm posting this prior to looking at any of the examples.
The 10 records was just an example here.  I believe the form we use has at least 15 but less than 20.  It is actually oriented in landscape too.  However, none of those details are important, but I'm trying to get an idea for how to implement this using MVD.  I have other projects that will also make use of this technique.
.
sparrow:
Yes, I was considering a temporary table like you described.  I had also thought about capturing a PDF once the order had been processed (and would no longer be changeable). Then removing those excess null fields altogether.
.
derek:
Again, that is exactly what I would like in FastReport.  Do you happen to know which version that is?  If I were to purchase a separate copy of FastReport, would it able to work with MVD (even if it uses the older version)?  I'm sure they have probably changed the file format by now tho...
.
ehwagner:
Thanks for chimming in with the extra cents.  You have me even more curious now with the overlay.  I have noticed that overlay group before, but had no idea how to use it.  It sounds like you have a good example for me to learn how it works tho.
.
sparrow:
Regarding your question about > 10 lines-  You are correct.  The additional lines would go on to page 2, but as I mentioned, it's closer to < 20.  Rarely do they order more than 1 page at a time.
.
I greatly appreciate all your efforts, comments and examples here!

"Energy and persistence conquer all things."