Hi Trent,

But I have already PK field that is an Auto Increment, and you cannot have 2 fields like that in the same table. I tried that and did not work

I think another solution like a generic class that you use in your medical application could be very helpfull these days. When I used the VFP framework like the ProMatrix one, that was a given.

That will be nice to see something like that in the next update...

More features more sales...

Thanks,

Doron

If you want SQL Server to generate it, just create the column as auto-incrementing and provide the seed just as you would for an auto-incrementing PK.  In this case, you would just not make it a PK field.

Hi Trent,

Regarding an Invoice ID which is a unique number and how would you really create that in the ms sql 2005? You are allowed just one counter per table. In the last training we had a discussion about a class that will be created in some point. But maybe there is a direct way to implement that in the sql server level. 

Regards,

Doron

You can have as many as you need and this is the recommended way to do things and is what we do.  However, if you need the old style of NextId, then you can do that too

Hi Trent,

For now these tables will be used initially in the application to setup some lookup values, so I am pretty sure only the Admin will have access to those forms, but I will enhance it to use an old fashioned NextID routine since there are a couple of those I need for Cash Receipt and Invoice.  I don't know if with SQL2005 you can have as many AutoIncrement fields as you want, but if not then a NextID routine will be needed to handle those numbers.

Thanks for the tip on MyBo.MoveAbsolute I'll keep it in mind since there are some data manipulations I need to do in this project but have not get there yet, so be ready for my questions

Edhy Rijo

Looks good...as long as you don't plan on two people hitting this table at the same time, then you could have an issue.  If you do not want to allow an auto-incrementing field to do this for you, then I would recommend creating a good ol' fashioned NextId table that is incremented with every query, this way if you have two, ten, or a hundred people hit this at the same time you will not have an overlapping of values.  Otherwise, your code really does look good.  The only thing I might mention would be the code you posted initially.  You were using MoveLast and the MovePrior.  You can do that, but you could do this in one line instead of two or three:

But keep up the good work...it looks good!

Looking good Edhy!



The FieldNames is an enum that is defined within the designer file of the BO. If you look in the Object Browser, find you BO, the next object in the list will be the BO. If the BO is named MyBO, then right after it will be an enum named MyBOFieldNames. If the BO is named YourBO, then right after it will be an enum named YourBOFieldNames.



I don't believe you can get the field name from the strongly typed property itself (i.e. you can't get the field name from Me.LineOrder).



Glad you're getting it going!

Hi Greg,

You definitely get me going

I made some modifications and checked the return value to foolproof the function.  I tested with a blank table and it worked perfectly.  I stick with the MAX() for simplicity, anyway those tables will not have many records anyway and they are properly indexed.  Here the new version:

Of course I cheated a bit with the loValue code above which I copied from the BO designer file.

For the Field Enums I only found a property Me.FieldEnums and frankly I could not found a way to use it to return the field name, but in this case it may no be necessary since the field name is the same in all tables "LineOrder", but it will be good to know how to get the field name from the field object Me.LineOrder.

Thanks again!

Edhy Rijo

I might make a couple of suggestions:



- You don't need to use the ToString() on LineOrderFieldName or TableName. They are already a strings

- Another way to get the same result as using the MAX function, is to use sorting and the top directive. I'm not sure which is faster, assuming that the LineOrderFieldName is indexed, but thought I'd throw out another idea. I.e. you sql would be something like:



- Use String.Format. It's a good habit to get into and it deals with strings more efficiently, as I recall. So now you might do the SQL String like:



- When the BO is created there is an enum created that provides access to all the field names. It will be within the BO and have a name like MyBO.MyBOFieldNames. You could then just use the ToString() on the field (you'd replace MyBO with the actual name of your BO). I'm not sure this helps, but it does provide a strongly typed access to field names, so if you change the name, code won't compile and you can find the problem quicker:



- This will fail if there are no LineOrder records found. I.e. the Select will return NULL as there is no data yet, and NULL + 1 is still NULL. So, you should either check the return for NULL (DbNull.Value) in you BO method or add to the SQL to handle it.



Good luck!

Hi Greg,

I ended up creating a function "GetNextLineOrder" in the BO which will query the table for the next available number.  Please see code below.

Only thing I could not figure out is how to pass the FieldName as a string instead of hard coding it in the Service_SetDefaultValues in order to make the call more generic since I will be using this code in several BO's.  Again, thanks for all your advice and pointing me in the right direction.

P.S.

I will add some TRY...ENDTRY error validation in case something happend in two months

Edhy Rijo