Grouping clients by ID number 2

[Steve-vfp9user]

Hello all

I have created a small application with a list of clients I do jobs for.

Each client has an ID number stored in a field called CLIENTID N(8) - Table MYCLIENTS

When I create a job this is given a Job Number and also a URN in a field called JOBLINK N(8) - Table THEJOBS

When items are logged against the job, they are linked with the same JOBLINK N(8) number - Table LINKITEMS

This all works fine.

If I add another job for the same client, this is given a new Job Number and new URN but the CLIENTID is also stored against the new job.

Example:

Client - Dave Anderson (Client ID 15)
Job number 100/2021 - Paint a wall

Client - Dave Anderson (Client ID 15)
Job nunber 101/2021 - Paint another wall

Client - Bill Harrison (Client ID 21)
Job number - 102/2021 - Remove a door

Client - Dave Anderson (Client ID 15)
Job nunber 103/2021 - Paint a ceiling

So what I am trying to achieve is for Client Dave Anderson's jobs to be consolidated as one:

Dave Anderson
Job number 100/2021 - Paint a wall
Job nunber 101/2021 - Paint another wall
Job nunber 103/2021 - Paint a ceiling

I have looked at the help file and found something called GROUP BY but I am not that knowledgeable with SQL so perhaps there is a different way of doing this.

SELECT Select_List
   FROM Table_List
...[WITH (BUFFERING = lExpr)]
   [WHERE Conditions]
   [GROUP BY Column_List]
   [HAVING Conditions]
   [UNION Clause]
   [ORDER BY Column_List]
   [INTO Clause | TO Clause ]
   [Additional_Display_Options]

Once I have created my list I will be printing the invoice (grouped) but then I need to replace a field (INVSENT) with a "Y" to show it has been printed/sent. I can update it accordingly once (and when!) it's been paid.

If you need any additional information, please let me know.

Any pointers or suggestions would be very much appreciated.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

No GROUP BY in SQL means something different from grouping together records. It's a step beyond that as its goal is to create one record per group that aggregates or summarizes it.

What would be a good example of using group by is computing the total of the prices by
Select sum(jobprice) as invoiceprice from thejobs group by clientid

Well, and if you do this alone you get the totals for all jobs AND a record for each client. You don't need any of this, as the invoice report can total the single job prices to the invoice total and you only want records for one client.

So what you look for is simply Select * From thejobs where clientid=15, for example.
Or a report used with FOR clientid=15

On top of each condition also AND invsent="N" to only bill unpaid jobs, of course.

You don't need complex or fancy SQL here, the type of grouping you want is what report groups are for, just grouping together rows, not merging them to one record of aggregated and also usually just partial data.

For example an average person age would require SQL like SELECT avg(date()-birthdate)/365.25 From persons. First, this is the most general group by you have without specifying anything to group by, the usage of AVG is then telling you only want one result value. And that result obviously only has partial data of all the data about the persons, as you don't create the average person with it, what name should that average person have, i.e. there is no AVG(name), AVG() only works on numeric data.

To summarize: Report groups and SL groups are not the same. An SQL group actually starts with all the records fulfilling the GROUP BY condition, but then all these records are somehow accumulated into one result record, not just grouped in the concept of sorting and layout of a report in grouped rows.

Chriss

 

[Mike Lewis]

Steve,

You don't need to do a GROUP BY here, as you are not aggregating the data in any way, that is, you are not calculating a sum, average, maximum or similar. A simple join should do the trick:

SELECT cl.Client_ID, cl.Client_Name, job.Job_Number, job_Job_Description ;
  FROM MyClients cl INNER JOIN TheJobs job ON cl.Client_ID = job.Client_ID ;
  INTO CURSOR csrResult

This will give you a cursor (named csrResult) containing four fields: the client ID, the client's name, the job number and the job's description. (You will need to change the field names to match the actual names in your data, of course.)

You can then use that cursor to drive your report. Within the report (that is, your invoice), you should group on the Client ID. In the group header, place the client's name. In the detail band, place the job information. (Presumably you will also need the client's address, the cost of the jobs, and other information; you can SELECT that into the cursor in the same way, but the above code will give you a start).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to add ...

I specified INNER JOIN in my code, above. This will result in a cursor containing only those clients who have jobs. This is presumably what you want for you invoices. If you wanted a cursor containing all clients, even those without jobs, you should change it to LEFT OUTER JOIN. In that case, in the records for the clients without jobs, the job-related data would contain NULL.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Hello Chriss

I appreciate the comprehensive reply which makes sense. It’s pleasing when a reply is posted that relatively new users like me can understand.

I’ll dig deeper with your suggestion.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Steve-vfp9user]

Mike

Didn’t see your post until I’d hit the button. I appreciate your contribution which is something for me to look at too.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Mike Lewis]

Steve, let me add one further point - not a very important one.

Your primary key, that is, your Client ID, is N(8). That's fine. But it would be more efficient to make an integer, that is, type I. An integer takes only half the space of a N(8). That in turn will reduce the size of the index, which will speed up any queries, joins, sorting or grouping that uses the index.

It probably won't make a noticeable difference to most small or medium tables, but it is worth keeping in mind. (And you won't need to change any of your code.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Cheers Mike

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

If you'd want to print a series of invoices, one for each client, you could print thejobs, sorted mainly by clientid and then the jobs date, perhaps, obviously also only for not yet paid jobs.

You can sort this way simply by SELECT * From thejobs order by clientid, jobendeddate where invsent="N"
Or you create an index on bintoc(clientid)+dtos(jobenddate) and set this order before you report FOR invsent="N"

The report itself then should have groups grouped by clientid, with a new page for a new group (=new separate invoice). The group then can have a list of jobs and the group footer would print the total, which you can sum on the fly while printing using a report variable.

Chriss

 

[Steve-vfp9user]

I’ll be looking into these suggestions over the weekend.

Much appreciated.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Steve-vfp9user]

Hello all

Having looked through this thread at your valued suggestions, I'm no nearer trying to achieve my goal. It's not your suggestions but my lack of understanding as this is still relatively new to me.

As explained, I may have several jobs for the same client on different days but only wish to send out a statement of unpaid jobs linked to that client.

The fields that make the job live are:

active="Y"
delivered="Y"
completed="Y"
invoicepyn="N"

So with that in mind, I am trying to consolidate all the jobs in a table called THEJOBS whereby the conditions are met as above, that will produce a report with the relevant linked products from the table LINKITEMS which are linked by a field name called JOBLINK but only show the Client details once.

Here's my example again:

Client - Dave Anderson (Client ID 15)
Job number 100/2021 - Paint a wall

Client - Dave Anderson (Client ID 15)
Job number 101/2021 - Paint another wall

Client - Bill Harrison (Client ID 21)
Job number - 102/2021 - Remove a door

Client - Dave Anderson (Client ID 15)
Job number 103/2021 - Paint a ceiling

So what I am trying to achieve is for Client Dave Anderson's jobs to be consolidated as one:

Dave Anderson
Job number 100/2021 - Paint a wall
Job number 101/2021 - Paint another wall
Job number 103/2021 - Paint a ceiling

Bill Harrison
Job number - 102/2021 - Remove a door

I am not expecting anyone to give me the answer but perhaps an explanation of the SQL statement Mike has posted or an easier understanding of Chriss' post.

I did see in the help file: SELECT DISTINCT so I'm wondering if that would achieve anything or am I barking up the wrong tree?

Much appreciated.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

Steve,

as said already, you only need to order data by client and jobnumber.
The grouping is done in a report.

Note again, you want to have all detail jobs per client, with each job number and jod description, etc.
That means neither SQL-GROUP BY nor DISTINCT help you, they reduce rows to 1 record and you lose the details you want to print.

The solution is in the report designer, create a new report, pick "Data grouing" from the Report menu. Then click Add and you're asked by what to group. Well, that's the clientid.

Now you have several options, but first just leave them as they are and click OK.
In the report design you'll see new bands added. Now pull down the group header and group footer band. In the white regiosn above them you can print the client name in the header and the toal of the job prices in the footer, for example.

And to sum the total you again don't need SQL, you need a report variable. That's also in the Report menu and there you also first click Add, name the report variable totalprice. In the combo box "Reset Value based on" you pick "Group: clientid" and from the calculation type combo pick "Sum".

Then add a report field printing totalprice, the variable, in the group footer.

The group detail should have job number and title.

Chriss

 

[Mike Lewis]

Steve,

You asked for an explanation of the SQL code I posted. To start with, I would say not to rely on the VFP help file for that. The help topics are good for checking details of the syntax, but not for understanding the basic concepts of SQL. You would do better to search for some basic SQL tutorials, of which I am sure there are many.

Regarding my code, this is a reminder of what I posted:

SELECT cl.Client_ID, cl.Client_Name, job.Job_Number, job.Job_Description ;
  FROM MyClients cl INNER JOIN TheJobs job ON cl.Client_ID = job.Client_ID ;
  INTO CURSOR csrResult

I'll break that down into separate parts:

SELECT cl.Client_ID, cl.Client_Name[COLOR=#BABDB6], job.Job_Number, job.Job_Description[/color] ;
  FROM MyClients cl [COLOR=#BABDB6]INNER JOIN TheJobs job ON cl.Client_ID = job.Client_ID[/color] ;
  INTO CURSOR csrResult

This simply says to take two fields from the Clients table and place them in a new cursor.

SELECT cl.Client_ID, cl.Client_Name[highlight #FCE94F], job.Job_Number, job_Job_Description[/highlight] ;
  FROM MyClients cl [highlight #FCE94F]INNER JOIN TheJobs job ON cl.Client_ID = job.Client_ID[/highlight] ;
  INTO CURSOR csrResult

This now adds two more fields to the cursor. But as these come from a different table (the Jobs table), we need to join that table to the Clients table. The ON clause says how the two tables are related: the Client_ID from one of them must match the Client-ID from the other.

The keyword INNER says that the join will only apply where the Client_ID in the Client tables has matching Client IDs in the Jobs table. In other words, if a given client has no jobs, that client won't show up in the results cursor.

Finally, we have the local aliases:

SELECT [highlight #FCE94F]cl.[/highlight]Client_ID, [highlight #FCE94F]cl.[/highlight]Client_Name, [highlight #FCE94F]job.[/highlight]Job_Number, [highlight #FCE94F]job.[/highlight]Job_Description ;
  FROM MyClients [highlight #FCE94F]cl[/highlight] INNER JOIN TheJobs [highlight #FCE94F]job[/highlight] ON [highlight #FCE94F]cl.[/highlight]Client_ID = [highlight #FCE94F]job.[/highlight]Client_ID ;
  INTO CURSOR csrResult

The purpose of these is to tell the code which table the corresponding fields come from. They work just like work area aliases in VFP, except that they are local to this SELECT statement. So the first two fields come from the Client table and the other two fields from the Jobs table. The names themselves have no special significance; I invented them just for this code.

You mentioned that you only want the report to show clients where the jobs are live. You would do that with a WHERE clause. However, I suggest you absorb what I have posted here first, and then we can consider the WHERE clause separately (and that's also because my dinner is almost ready and I am getting hungry).

Try working with the above code separately from your report, for example, from the command window. Then browse the resulting cursor to see if it if it has worked.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Mike & Chriss

I appreciate your time and posts which I'll endeavour to study!

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Steve-vfp9user]

Here is my update:

Having taken onboard the advice from Chriss and Mike, I created a report in the following way:
[ul]
[li]Modify report testinvoice[/li]
[li]On the reports drop down menu, I selected "Data Grouping"[/li]
[li]At this point, I couldn't understand why my field tables weren't shown in the "Group on" section but some research suggested that the table had to be open[/li]
[li]Having added the relevant field (CLIENTID) to the "Group nesting order" section, I clicked ok which subsequently created two additional bands on the report called "Group Header 1: clientid" and "Group Footer 1: clientid"[/li]
[li]I added two tables to the reports Data Environment (Those being THEJOBS and LINKITEMS) and added some random fields from the LINKITEMS table to the Detail band.[/li]
[li]I previewed the report and yes, it worked[/li]
[/ul]

I can now work on the conditions to produce the relevant report which I'm ok with.

Needles to say, I'm very grateful to Chriss and Mike for their continued contributions (and inspiration) on this thread and the forum.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Mike Lewis]

Good to see you've got it working, Steve. Come back if you need further help with the WHERE clause.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Hi

Not sure if I should create a new thread, but it is linked to my original one.

Having successfully created the report, I am now able to issue the following command which produces the desired result:

minvoiceno=0
USE DEFAULTS SHARED
minvoiceno=INVOICENO
REPLACE INVOICENO WITH INVOICENO+1

USE THEJOBS SHARED
GO myrecno

mclient=0
mclient=14 && This could be any number but I'll use this one for now

REPORT FORM GROUPSTATEMENT NOCONSOLE PREVIEW FOR ;
  ACTIVE="Y" AND DELIVERED="Y" AND COMPLETED="Y" AND ;
  INVOICEPYN="N" AND SELECTED="Y" AND CLIENTID=mclient

The above produces the report after which I issue the following:

REPLACE ALL INVSENT WITH "Y", INVDATE WITH DATE(), INVOICENO WITH minvoiceno FOR ;
  ACTIVE="Y" AND DELIVERED="Y" AND COMPLETED="Y" AND ;
  INVOICEPYN="N" AND SELECTED="Y" AND CLIENTID=mclient

The above replaces all the relevant fields to the invoice that has been produced as there may be more than one job against the client as previously discussed.

My question here is, if the above produces one invoice statement, how can I add the same information as the report produced to relevant tables so I can look back at it if needs be?

Example: The above might produce three jobs against one client with the same INVOICENO. I can copy the tables THEJOBS, LINKITEMS and linkmaster them by INVOICENO.

I hope that makes sense but if you require any further information regarding my quest, then please ask.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

The simple obvious solution is to print copies, or save the PDF, but I know you just want the printed data as reference in case of unpaid invoices, questions or complaints.

Well, the FOR clause you use is almost like a query, you could do this before or after the report, but also before you do the REPLACE, obviously:

COPY TO reportdata.dbf FOR ;
  ACTIVE="Y" AND DELIVERED="Y" AND COMPLETED="Y" AND ;
  INVOICEPYN="N" AND SELECTED="Y" AND CLIENTID=mclient

That would also go well with the usual way to create a cursor before running a report on it and not work with the FOR clause of REPORT FORM, but simply print all cursor data prepared and fed into the report.

If you have long field names (dbf tables part of a dbc) then you could get a reportdata.dbf with the same long names, if you first simply CREATE DATABASE reports and add the clause DATABASE reports to the COPY TO command.

See COPY TO for further features like using a FIELDS clause.

This will not take into account relations into detail tables, though. You better do an SQL query that joins all necessary data for having a single simple cursor with data for the report.

On the other side, I wouldn't necessarily recommend to store that data on a report for report basis. You do have the invdate field to tell you this data has been printed on that date, so you can refer back to it by that field in combination with the clientid, or you explicitly add a field for a reportcode that you create and also print on the report. I would thinkk the invoice number would also do this job, so finally you get all data for aninvoice by simply querying SELECT * FROM atable WHERE invoiceno = x. You already have these fields to get back to the data.

What a report not does is provide a cursor of printed data, if you thought it might be that easy, but that's also the reason many query all data necessary into a cursor before running the report on all of that data, without using relations and the FOR clause. And the other thing is having such staus data or in the most generalized case audit data, that is storing data as is before any updates. There are stored procedure that do such tasks automatically, but that needs a DBC. And as a result you have all data in all states it ever has been, not only on the basis of changes you do after a report run.

Chriss

 

[Steve-vfp9user]

Post noted Chriss

Thank you

Steve Williams
VFP9, SP2, Windows 10