VFP Reports - Changing Names 4

[ostrowlaw]

I don't know how to describe my problem. I have a report that I use for billing. Simple fields, Lastname, Firstname. So, this past month I had 2 bills with the same last name, let's call them Jane Cruz and John Cruz.

My Pageheader is ALLT(LASTNAME)-" "ALLT(FIRSTNAME) and in the Field Properties I have it to "Print When", Print Repeated Values set at yes.
Everything works great except when I have 2 entries with the same last name. Instead of printing 2 separate bills for $350.00 each, I get 1 bill for $700.00. The name of the second bill (John Cruz) doesn't print out, but the text of the bill does print out as if it was part of Jane Cruz's bill.

How do I split them up? Ideally, I would like it to continue printing for the same Lastname+Firstname but when the firstname changes the bill should change.

I hope I've put enough information in here.

Thanks for your help, I'm not a computer programmer, just a Foxpro user.

Alan

 

[Chris Miller]

ALLT(LASTNAME)-" "+ALLT(FIRSTNAME)

Is that literally copied over from the report as is?

Aside from a little missing + I added here the value for the same last name but different first name does change, you would need to have two people with exactly the same name to merge two bills.
Why don't you group by a bill specific unique ID? Using grouping in the report you don't need to print what you group by, but it's the best way to split different bills.

Chriss

 

[ostrowlaw]

Well, I have some clients that use an account number, some use different types of numbers, so I always used lastname + firstname.
For some reason, this report only uses the lastname to sort and, as discussed above, it merges different accounts with the same lastname.
How would I change this so it used Lastname+firstname instead of just lastname?

Thanks
Alan

 

[Mike Lewis]

Alan,

The sorting is actually done before you get to the report - either by setting an index or creating a cursor. Without knowing exactly how you do it, it is difficult to know what to suggest.

But I wonder if you mean "grouping" rather than "sorting" - in other words, the expression that the report uses to break the data into individual bills. If that's the case, then the expression should be [tt]ALLTRIM(Lastname) + " " + ALLTRIM(Firstname)[/tt] (which I think is what you already have).

By the way, it would be helpful to know what is in your Print When box.

But, in general, I would go with Chris's advice: use a unique account ID rather than (or, rather, as well as) the customer name. I take your point about different clients having different types of account code. But a unique ID would be in addition to such codes. It would be internal to your system - not visible to the client. A simple consecutive integer is all you need. VFP makes this easy, with the [tt]Integer (Autoinc)[/tt] data type.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Olsson]

I agree wholeheartedly with Mike. What if you get two customers with the exact same name?

 

[ostrowlaw]

Thanks Mike and Chris. I do definitely have an index file attached to the database file. Its been so long I can't remember whether it is indexed on lastname only or lastname+firstname.

I am attaching what the report looks like and the Print When:

 

[Mike Lewis]

That's helpful. I see that you are in fact grouping on Firstname within Lastname, and this completely explains the behaviour you are seeing. When the report sees a change of Lastname, it will print all the data for that Lastname, without waiting for a change in Firstname.

The quick solution would be to remove the Group 2 band, and to set the Group 1 Expression to [tt]LASTNAME + FIRSTNAME[/tt]. You don't have to worry about trimming the names or inserting a comma at that point. The change in the underlying data will do the trick.

But that doesn't answer Dan's point: two customers with exactly the same name. By no means impossible. You could also run into problems caused by slight variations in the spelling of customers' names. I see that you already have an ACCOUNTNO field. I suggest you consider using that as your Group 1 expression.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

On a side point, you are printing this expression in the invoice header:

ALLTRIM(LastName) [highlight #FCE94F]-[/highlight] ", " + ALLTRIM(Firstname)

That minus sign (which also appeared on your first post in this thread) is perfectly valid syntax. But it has no effect on what is printed The effect would be to add a single comma after the last name, and then a space, and then the first name. But it is redundant, because the first ALLTRIM() is doing the same thing.

This has got nothing to do with your main problem. I am just mentioning it out of interest - and in case anyone seeing this thread mistakenly thinks that the above expression will throw an error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ostrowlaw]

Thanks you Mike. I'll try to modify the report and see how it goes. I'll be back. . .

 

[ostrowlaw]

YES!!! Thank you, I think that did it, I just removed the Group 2 band and changed Group 1 as you suggested, ran a sample bill and it looks exactly the same. I don't have any duplicate names this billing cycle, but I am assuming that this is the fix.

Thank you for your kind assistance! It is much appreciated.

 

[ostrowlaw]

I was thinking about the theoretical problem, i.e., 2 records have the same name. This is possible (although I haven't had this occur in quite some time - it was a mess!). So, if I Changed Group 1 band to Lastname+Firstname+Accountno do you think this would resolve the problem? The reason I would need the have the last & first names as well is because not all clients have an account number.

Thanks
Alan

 

[Chris Miller]

Lastname+Firstname+Accountno do you think this would resolve the problem?

What would resolve your problem is what Mike suggested earlier: an ID which you create yourself. I know that customers want their own accountno or a different number they want printed on the bill, but the grouping should be done on something that always unique and the simplest is an ID. Every table you have should have an ID, a primary key anyway. So take your account table and add such a field, this way:

USE accounts EXCLUSIVE 
ALTER TABLE accounts ADD COLUMN id INTEGER AUTOINC PRIMARY KEY

If your table is a free table, you'll get the error "Feature is not supported for non-.DBC tables." Then you can still have an almost primary key, a candidate key, which also must be unique:

USE accounts EXCLUSIVE
ALTER TABLE accounts ADD COLUMN id INTEGER
UPDATE accounts SET id = RECNO()
ALTER TABLE accounts ALTER COLUMN id INTEGER AUTOINC NEXTVALUE RECCOUNT()+1
INDEX ON id TAG id CANDIDATE

Once you have that, you can make accounts.id the group 1 expression, or first select data into a report cursor and then select accounts.id as accountid and make accountid the group 1 expression.

If you still get an error, then your tables must be an even older format than VFPs free tables, then make a move by recreating tables in VFP. To recreate a table take a look at COPY TO ... WITH CDX. If you have other index files than CDX you need to recreate them. The copied table now has a VFP table header and then features like AUTOINC and making such tables transactable are possible, so that's a bonus for future changes.

Here's a snippet to start from (untested)

USE accounts.dbf
COPY TO newaccounts.dbf WITH CDX
USE in accounts && close old accounts DBF
SELECT newaccounts
ALTER TABLE newaccounts ADD COLUMN id INTEGER
UPDATE newaccounts SET id = RECNO()
ALTER TABLE newaccounts ALTER COLUMN id INTEGER AUTOINC NEXTVALUE RECCOUNT()+1
INDEX ON id TAG id CANDIDATE
... same for further tables

Chriss

 

[Mike Lewis]

Just to add a word to Chris's latest post ...

Whenever I design a database, I make it a rule for every table always to have a primary key; the primary key is always named ID; and it is always of type Integer (AutoInc). That way, I never have to think about it. When adding a new record to a table, I never have to worry about generating a unique primary key for it. And when linking tables, I never have to stop to think of the name of the primary key.

It might be too late for you to do this, Alan, but it is something you should definitely keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ostrowlaw]

Thanks Chris and Mike. I'm going to print this off and keep it for the future. Its too late at this point.
These databases (tables!!!) go back to when I originally started using dBaseII (under CPM as the operating system) a million years ago, before we had all these features and functions. In those days (of the dinosaurs) users like me who were not programmers needed to learn how to do some rudimentary stuff just to get bills out to clients and keep track of matters. Looking at the above, the ability to generate a random id number would've solved so many problems over the years but, alas, everything in this system relates back to firstname, lastname, accountno.

I think I'm going to changed the Group 1 band to Lastname+Firstname+Accountno and see how that goes.

BTW, the VFP report program is very complex but it makes life so much easier than having to program everything from scratch.

 

[ostrowlaw]

Hi again,

Sorry to bother you guys but although I've been using Foxpro for many years (since 2.0 and before that dBase II), I've just recently started playing around with reports. The reports are much easier than writing entire programs to do the same thing, but the syntax is arcane.

Anyway, I got how to make changes for each particular client's needs as discussed above. Now I have a client that wants a separate invoice for each case - with a separate header page on each new invoice. Is there some way to tell the report to reprint the header when Lastname+Firstname changes?

Thanks as always
Alan

 

[TamarGranor]

Set up a group based on LastName+FirstName and put that information in the group header.