Trust ID PBCEX logo 300x102

Count number of printed cards with CSV database

12 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Topic Author
Count number of printed cards with CSV database #39
Question: Comma-separated files do not allow updating database records. So I cannot update a print counter in the table that contains the card-holder data. Is there a possibility to keep track of the number of cards that have been printed?

Answer: Please, find enclosed an example of a CSV connection that keeps track of the number of cards that have been printed. It makes use of four advanced features.

1. Use the advanced options of the storage items
In the enclosed example, the card-holder info is stored in the file Names.csv. Each time a card is printed, a record is added to the Cards.csv. How this is done is defined on the storage-item page of the data-definition wizard. The record contains a card number (which has been mapped to a global counter), the name of the card holder and the ID of the card holder. Note that the cardholder ID is always stored and shouldn’t to be defined explicitly as a storage item. Furthermore, the advanced options make sure that it always adds a record and never tries to update (which is not possible for CSV files). So in the end, Cards.csv will have one record for each printed card.
[attachment=2:2gamhgnj]<!-- ia2 -->Storage Items.JPG<!-- ia2 -->[/attachment:2gamhgnj]
[attachment=1:2gamhgnj]<!-- ia1 -->Advanced Options.JPG<!-- ia1 -->[/attachment:2gamhgnj]
2. Use the internal-view option
An internal view makes sure that the Cards.csv file is joined with the Names.csv file to calculate the number of printed cards.
[attachment=0:2gamhgnj]<!-- ia0 -->Internal View.JPG<!-- ia0 -->[/attachment:2gamhgnj]
To be continued in the next post...

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
Attachments:
12 years 9 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Topic Author
Count number of printed cards with CSV database #40
3. Switch on the level-three filter
The level-three filter has been switched on. Each time F5 is pressed, an advanced filter window appears that allows to select a certain ID range and a maximum number of printed cards. Leaving a text box empty means that the condition is neglected, so the example below only selects IDs 1 to 1000. Typing a 0 in the third condition, would make sure that only names are shown of people whose cards have not been printed yet.
[attachment=1:w5ay4u0z]<!-- ia1 -->Level-Three Filter.JPG<!-- ia1 -->[/attachment:w5ay4u0z]
4. Use the schema.ini file
The schema.ini file defines some properties of the CSV files. It is only needed when the file structure deviates from the system standard. In the present example the CSV files are comma separated, but the standard on European systems is to use the comma as decimal separator and the semicolon as field separator. Therefore I used an explicit schema.ini file. The schema.ini has the following content:

[Names.csv]
Format=Delimited(,)
DecimalSymbol=.
Decimal
[Cards.csv]
Format=Delimited(,)
DecimalSymbol=.


Download the entire data folder that contains this example:
[attachment=0:w5ay4u0z]<!-- ia0 -->Data.zip<!-- ia0 -->[/attachment:w5ay4u0z]

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
Attachments:
11 years 10 months ago
Hsaenz
Fresh Boarder
Fresh Boarder
Posts: 1
More
Count number of printed cards with CSV database #709
Would this option also work for another database format, say, SQL?
11 years 10 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Topic Author
Count number of printed cards with CSV database #710
Hi Hector,

For Excel, Access, SQL Server, etcetera, the whole thing doesn't apply, because you are allowed to update records, so you can store the print counter directly in the card-holder table and define a filter on that field.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)