Create New Records if Not Exist 2

[abbasaif]

Hi,

Can I generate auto increment in alphabets as shown in image of the combo box.

These Roll no. generates on the basis of Job Order No. The Job Order No. starts with 'JO' whereas Roll No. starts with 'RO' plus the rest of the job order no.

If I click on (+) sign, it should generate "Roll No." itself which is not exist in the record. In this case the new Roll No. should be 'RO118214C'.
The Field name is Rollno c(15).

Please let me know how can I generate it?

Thanks

Abbassaif

 

[Mike Lewis]

The usual way to do this would be to maintain a separate table whose only job is to generate the required numbers. It contains one record, with one numeric field.

When you create the table, set the value of the field to the highest unused number. When you need to generate a new number, add one to the value of the field, and use that number in your new record.

Now, the important point: If this is a multi-user system, you will need to lock the number table just before you increment it, and unlock it immediately afterwards. If you didn't do this, you risk two users using the same number, which is not what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[abbasaif]

Thanks Mr.Mike for the reply.

I usually do the same which you explained. But it is slightly different case. It is character field in the last.
And, I want to generate A,B,C,D and so on...

Regards

Abbassaif

 

[abbasaif]

Sorry, I forgot to attach Image.

Regards

Abbassaif

 

[Mike Lewis]

Making it a character field should be no problem. The field in the special table will be numeric, but you can convert it at the point that you create the new record. So if the new value is contained in nNew, the string for a roll number would be something like this:

[tt]cString = "RO" + PADL(nNew, 15, "0") + "C"[/tt]

It's not clear from your question how to determine the value of the final letter. I have used "C" here, but you would need to insert the actual value in each case.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If you have a value JOnnnnnn in a field, what hinders you to copy it and on the way change J to R? You SET or INSERT RollNo with STRTRAN(Jobno,"JO","RO")

To add letters A,B,C,D also simply is done by +"X". To maintain this as a counter/subcounter is not a thing for autoincrement, that's a) only about numbers and b) will not reset to 1 unless you use foreseen commands to do so, but that requires exclusive access. So overall autonincrement is not forseen for subcounters, you have to do this on your own.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Hi,

It's not clear from your question how to determine the value of the final letter. I have used "C" here, but you would need to insert the actual value in each case.

The Value of the final letter must be in sequence not random.

Regards

Abbassaif

 

[abbasaif]

Hi,

I did something like this:

mJono = This.Parent.txtJono.Value
Select tmpRoll
Count To totrec
Do Case
   Case totrec = 0
      mRollno = 'RO'+Substr(mJono,3,8)+'A'
   Case totrec = 1
      mRollno = 'RO'+Substr(mJono,3,8)+'B'
   Case totrec = 2
      mRollno = 'RO'+Substr(mJono,3,8)+'C'
   Case totrec = 3
      mRollno = 'RO'+Substr(mJono,3,8)+'D'
   Case totrec = 4
      mRollno = 'RO'+Substr(mJono,3,8)+'E'
   Case totrec = 5
      mRollno = 'RO'+Substr(mJono,3,8)+'F'
   Case totrec = 6
      mRollno = 'RO'+Substr(mJono,3,8)+'G'
   Case totrec = 7
      mRollno = 'RO'+Substr(mJono,3,8)+'H'
   Case totrec = 8
      mRollno = 'RO'+Substr(mJono,3,8)+'I'
   Case totrec = 9
      mRollno = 'RO'+Substr(mJono,3,8)+'J'
   Case totrec = 10
      mRollno = 'RO'+Substr(mJono,3,8)+'K'
   Case totrec = 11
      mRollno = 'RO'+Substr(mJono,3,8)+'L'
   Case totrec = 12
      mRollno = 'RO'+Substr(mJono,3,8)+'M'
   Case totrec = 13
      mRollno = 'RO'+Substr(mJono,3,8)+'N'
   Case totrec = 14
      mRollno = 'RO'+Substr(mJono,3,8)+'O'
   Case totrec = 15
      mRollno = 'RO'+Substr(mJono,3,8)+'P'
   Case totrec = 16
      mRollno = 'RO'+Substr(mJono,3,8)+'Q'
   Case totrec = 17
      mRollno = 'RO'+Substr(mJono,3,8)+'R'
   Case totrec = 18
      mRollno = 'RO'+Substr(mJono,3,8)+'S'
   Case totrec = 19
      mRollno = 'RO'+Substr(mJono,3,8)+'T'
   Case totrec = 20
      mRollno = 'RO'+Substr(mJono,3,8)+'U'
   Case totrec = 21
      mRollno = 'RO'+Substr(mJono,3,8)+'V'
   Case totrec = 22
      mRollno = 'RO'+Substr(mJono,3,8)+'W'
   Case totrec = 23
      mRollno = 'RO'+Substr(mJono,3,8)+'X'
   Case totrec = 24
      mRollno = 'RO'+Substr(mJono,3,8)+'Y'
   Case totrec = 25
      mRollno = 'RO'+Substr(mJono,3,8)+'Z'
Endcase

eMessageTitle = 'Quality Control Inspection Report'
eMessageText = 'Roll No.'+mRollno+' is Available. Do You Wish To Generate This???'
nDialogType = 4 + 16 + 256

*  4 = Yes and No buttons
*  16 = Stop sign icon
*  256 = Second button is default

nAnswer = Messagebox(eMessageText, nDialogType, eMessageTitle)

Do Case
   Case nAnswer = 6
      This.Parent.cboRollno.RowSource = ''
      This.Parent.cboRollno.DisplayValue = mRollno
      ThisForm.kField = .T.
   Case nAnswer = 7
      ThisForm.kField = .F.
      Nodefault
      Return .F.
Endcase

 

[Olaf Doschke]

Don't you know you could "compute" a character? It has an ASCII code, so look what this gives you:

? CHR(64+1), CHR(64+2), CHR(64+3)

It's the least problem you have in the bigger context of a table holding detail data with sub numbering, but that aside, really, all data is numbers in computers, also letters.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Your large DO CASE / ENDCASE is a heavy-handed way of achieving a simple goal. You could change the whole thing to something like this:

[tt]cString = "RO" + PADL(nNew, 15, "0") + CHR(65 + totrec)[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[abbasaif]

Hi

Thanks Mr.Mike,

I applied that command, like;

nNew='RO118214B'
TOTREC=2
cString = "RO" + PADL(nNew, 15, "0") + CHR(65 + totrec)

Result is showing

cString = 'RO000000RO118214BC'

I want

cString = 'RO118214C'

Regards

Abbassaif

 

[Mike Lewis]

So you start with 'RO118214B' and you want 'RO118214C'? In other word, you are just changing the final letter? If that's right, you want this:

[tt]cString = LEFT(nNew, 8) + CHR(65 + totrec)[/tt]

But I suspect that's not going to be right either. We had previously established that nNew is numeric. You are now not only saying that it is a character string, but that it also contains the prefix ('RO') and also some letter at the end, but not the letter you want.

Also, you originally said that the final string was to be 15 characters. You are now showing it as nine characters.

Unless you can express your problem clearly and consistently, it will always be difficult to give the correct answer.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[abbasaif]

Great Mike

So you start with 'RO118214B' and you want 'RO118214C'? In other word, you are just changing the final letter? If that's right,

Yes

cString = LEFT(nNew, 8) + CHR(65 + totrec)

Exactly I was looking for that

But I suspect that's not going to be right either. We had previously established that nNew is numeric. You are now not only saying that it is a character string, but that it also contains the prefix ('RO') and also some letter at the end, but not the letter you want.

I don't want increment in numeric field ("RO"+118214+"C"), it is fixed. Only the last letter which is from "A to Z"

The length I want to keep it 15 character. Because this is first stage. I will keep on increasing as the different stages passed.

Like;

If the Jumbo Roll is in Printing Section = The Code should be 'RO118214C. The red numeric will be changed based on Job Order "JO118214" which is fixed 8 Characters in length.

These Jumbo Roll can be divided into number of Rolls (may be more than 40). So, I was thinking that if the rolls reach the maximum limit of the Alphabets (26), I will start generating "RO118214C1. The Last character with numeric 1. So, there would be a huge room for me to generate the code. (First A-Z, Then A1-A9, B1-B9, C1-C9 ..... Z1-Z9)

Summary:
--------
Printing Section:
Job Order No.= JO118214
Roll No. = RO118214A .... RO118214Z9

Slitting Section:

Roll No. = 'RO118214A will become 'RO118214A-1' .... 'RO118214Z9-100'

So, because of that I kept the length upto 15 characters to make it unique throughout the tables.

Regards

Abbassaif

 

[Mike Lewis]

Glad you've finally got it sorted, Abbassaif.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

abbasaif,

If the Jumbo Roll is in Printing Section = The Code should be 'RO118214C. The red numeric will be changed based on Job Order "JO118214" which is fixed 8 Characters in length.

Yes, that's also why I earlier gave you STRTRAN(Jobno,"JO","RO"), change that according to whatever has your Jobnumber or Joborder.

Left(8) also doesn't do that, as it contains the JO, you already solved that part with your SUBSTR() (or you got that advice from another forum, perhaps), anyway, it should be easy enough to puzzle together what will give you the final composition.

Just as a side note: If the values of a field of a table always start with "JO" or "RO" there is no need to store that in the DBF, you could always add that when you print or output that number. In a form you could put a label with "JO" or "RO left of a textbox showing the number. Just an idea. That makes life much simpler.

And another hint of what your code will not take into account: Once you would have say the three IDs RO118214A, RO118214B, and RO118214C, then delete RO118214B and add another record, your method of counting will make totrec=2 and the next code you generate will repeat the "C". Think about how you'd want to handle this situation.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Thanks Mr. Olaf

Yes, that's also why I earlier gave you STRTRAN(Jobno,"JO","RO"), change that according to whatever has your Jobnumber or Joborder.

I will use this instead of:

mJono = 'JO118214'

mRollno = 'RO'+subs(mJono,3,8)+'A'

Just as a side note: If the values of a field of a table always start with "JO" or "RO" there is no need to store that in the DBF, you could always add that when you print or output that number. In a form you could put a label with "JO" or "RO left of a textbox showing the number. Just an idea. That makes life much simpler.

Only in printing section, "JO118214" is mandatory. We have to use it just to create the Roll No. and then in the rest entire process "Roll No." will be used.

And another hint of what your code will not take into account: Once you would have say the three IDs RO118214A, RO118214B, and RO118214C, then delete RO118214B and add another record, your method of counting will make totrec=2 and the next code you generate will repeat the "C". Think about how you'd want to handle this situation.

The Record will not be deleted. We have to generate the barcode of (RO118214A ... ) and paste on to the Jumbo Roll. So no question arise to delete the roll.

Thanks Mr. Olaf of your valuable hints.

Abbasaif

 

[Olaf Doschke]

Abbasaif,

let's get this very straight, as this obviously is very slowly getting to you. I'll take my time to show you all this and hope you'll get a final understanding.

Startup VFP, close down all windows but the command window and put that on the right side of the screen, so you can see the output on the left side.

Type in

mJono = 'JO1234567890'

and ENTER it. Yes, that's more digits than usual, it's intentional, just go with it, you later see why I used this.
We also need

totrec = 0

(or any number between 0 and 25)

Now let's compare the recommended expressions. Not all of them, just the latest solutions which were given after some misinterpretations have been cleared up.

Mike's latest tip (translated to the variables used in this session) was:

? LEFT(mJono, 8) + CHR(65 + totrec)

That outputs [tt]JO123456A[/tt], because a) it only takes 8 characters and then adds one with CHR(). As totrec=0, it follows [tt]CHR(65 + totrec)=CHR(65 + 0)=CHR(65)='A'[/tt]. The stress of Mikes solution is on generating the A, not changing from JO to RO.

Can you please understand this, can you please see the value of Mike's hint, that he gave you code that can replace the whole >50 lines of code you used with a single expression? The stress was not on using LEFT() instead of SUBSTR(), the stress was on using the totrec number within CHR(), so you don't need to cover 26 cases of code.

If you can't even see what is helpful about something it becomes extremely hard to help you.

I said you already had the better idea about using SUBSTR(), but that doesn't mean it's the better idea overall, it's better than using LEFT(), because LEFT does obviously start at the first position and doesn't remove the 'JO', but notice it's the right length, it only takes the 6 following digits after the JO. There you have the first reason I intentionally used a too long code [tt]JO]1234567889[/tt], to show how LEFT removes the unwanted ones. With SUBSTR() you are better than that because you start in the third position. Let's see what your code does without any of the +'A' to +'Z':

'RO'+Substr(mJono,3,8)

outputs [tt]RO12345678[/tt].

Now ask yourself why it ends in 78. You're thinking wrong about SUBSTR() parameters. thinking it'll extract from position 3 to 8, but that's not the case, the 3 is a position the 8 is a length. The intellisense help also shows that to you, while you type in that command.

The right way to use SUBSTR would be:

'RO'+SUBSTR(mJono,3,6)

I hinted back on the earlier tip to use STRTRAN():

STRTRAN(mJono,"JO","RO")

which outputs [tt]RO1234567890[/tt].

Yes, this also keeps too many digits at the end, but in case you start with the correct data this also is solving the change from JO to RO and it's only about that and it preserves what else you have. You can argue what's a better idea. It depends on the bigger picture and what happens in the future. I'll spare to dive into that, just notice that STRTRAN(mJono,"JO","RO") is very self-explanatory in what it replaces with what. Some will prefer SUBSTR as it acts on predefined positions and they prefer that. If you add more suffixes you might get into the situation JO is not just at the begin of the code. But overall STRTRAN(mJono,"JO","RO") is more intuitive, you can ensure it only does the first replacement with STRTRAN(mJono,"JO","RO",1,1) and no code is stable and future proof against any thinkable changes. STRTRAN() also is more intuitive to modify if the prefixes change and even become longer.

And again, look back please, this wasn't the only thing I said. I continued about how you simply add a suffix with the + operator. You took +"X" far too literal. It is common sense X stands for anything. It's something I can't tell you as it depends on so much. It wasn't obvious back then what you'd use to know which letter comes next, we talked about maintaining counters in a table of such metadata.

But is it hard now, to put together two partial solutions to one overall final solution? Please, now pause reading and think a bit about what would be a solution.

------------------

Some people often argue "Just give them what they need", especially with questions like yours. And this would be a prime example for them because it's all just about a small expression. Just note I see this as a prime example about why it's important to go into details. Nobody knows you'd COUNT TO totrec to determine the next suffix letter. It's not that specifically this COUNT that we don't know without you telling us, its using a separate table so it's easy to know which is the next letter. Especially now that you stated you'll never delete a record it's sufficient. But nobody can know that.

I also argue it's important because you already hint on how this all extends with even further suffixes and how you don't want to end with Z.

We're making assumptions about the parts you don't tell, assumptions that are most obvious and normal to us, we're not in your head, we don't know the full story and while a screenshot helps, it still doesn't tell data types or other technical information. Mike, for example, thought mJono or nNew was a number and used [tt]PADL(nNew, 15, "0")[/tt]. His post was after you posted the image, but look at the time stamps: 9 Dec 19 14:27 you posted the image you forgot initially, 9 Dec 19 14:35 Mike wrote his answer. He likely started writing before your post and as one writes the thread doesn't update, so he likely only saw this after his post.

Yes, it's really very detailed and observing to see that and take that into account, it has nothing to do with programming skills, too. But it's something everyone could see and besides all that never think anyone is writing anything in bad intentions.

Is this one of my rare rants? I hope it's recognizable I'm not simply ranting about any mistakes made. I also see your position, you're not able to come up with the necessary code and ask for help and nothing works fully.

But see, this is not about giving you 100% of what you need, nobody can do that in every case, as anything always has a context only known to the questioner. Even such simple things. And it's not asked too much you pull this together to a final conclusion.

Still not convinced, how this isn't simple and how it's always problematic to only hand out fish and not teach fishing?

Take this code and try to figure out why it doesn't work:

Create Cursor tmpjobs (jobno integer autoinc, jono char(15) default 'JO'+PADL(jobno,6,'0'))
Create Cursor tmpRoll (rono char(15))

Append Blank In tmpjobs 

Insert into tmpRoll values (Strtran(tmpjobs.Jono,'JO','RO')+Chr(65+Reccount('tmpRoll')))
Insert into tmpRoll values (Strtran(tmpjobs.Jono,'JO','RO')+Chr(65+Reccount('tmpRoll')))

So, to be clear, this is not meant as the solution, this contains an intentional error and there are several ways to make this work. I'll come back, but I'll leave it to you to figure out one way of solving that.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Thank Mr. Olaf for this detail explanation with examples.
Definitely I will go through this.

With warm Regards

Abbassaif