Help with Tablemax function

[Alastair]

I am using the 30 day trial of Alpha Five V9 Platinum to link to an Access table, so far so good!

One of the fields in this table Contains Job Numbers (which is a string)

JOB_NO
12345
23456
R23457

etc..

I am trying to use the Tablemax command to find the greatest value stored in this field (I want to filter out any JOB_No's that start with an R) this is the code I have tried:

dim OJN as c
OJN = tablemax("acc_orders.dbf", "left(JOB_NO,1)<>\"R\"", "JOB_NO")

but I get the following error (see tablemax.jpg), I have tried various different ways of doing the above but I still can't get it to work, is there an easier way or can anyone suggest what the problem is?

[Stan Mathews]

Re: Help with Tablemax function

In the interactive editor try

? tablemax("acc_orders.dbf", ".T.", "JOB_NO")

to see what the result is. That should help diagnose what is going on here.

[Alastair]

Re: Help with Tablemax function

Hi Stan,

Interestingly, I get the same error, when run from the pushbutton on the form.

When I run it from the interactive editor, I get the following message:

= <No data returned>

However, it works if I use the same code on a native alpha five table!

Does this command not work with an active link table?

[Keith Hubert]

Re: Help with Tablemax function

Hi Alastair,

Have you tried to use Arguments, this is where you can filter the data that gets returned from the data source.

The active link table is only a link to the data, it does not hold any data.

Following our chat, I have attached a thumbnail of records selected from the Alphasports.mdf table. I asked for only records where the last name was between the Start argument and the Last argument. I also asked for the Order to be on the LastName.

I hope this explains better how the Active link table works.

[Alastair]

Re: Help with Tablemax function

Hi Keith,

I took your advice and downloaded the new version, I must say I like some of the improvements I have seen so far very much!

I have only had the new version on my PC at work for a few hours so haven't seen arguments yet, I will have to look into these and see if they will do what I need.

The Job No data is a bit more complicated than I first thought! I couldn't remember how I had set up the old Access database, it seems Job No field consists of a 6 long char which contains the following prefixes:

12345 = Job No
R12345 = Quote
MW2345= ?? Entered by another user
A12345 = ?? Entered by another user

All I want to do is to find the max. of the valid Job Nos. e.g. ones without a letter prefix and add one to this number to calculate the new Job No. I will have to filter out all the Job Nos starting with a letter and then find the max. of the remaining results. Does this sound like a task for Arguments?

[Keith Hubert]

Re: Help with Tablemax function

Hi Alastair,

This sounds like you have got a real problem on your hands.

The field that holds the Job No should be just that. The status of the Job "R" should be in a separate field. The prefix of the of who the user was should also be in a separate field. These fields can all be concatenated for viewing.

With the data in your access app in separate fields then you can use the active link to call the records you want.

I cant see how you can do what you want, with the way the data is structured.

[Alastair]

Re: Help with Tablemax function

Hi Keith,

It is possible to filter them out! I wrote some code (along with a query table)to do it in Access a long time ago.

There is a reason for the two main different categories of Job No in the Access db. It was based on the old method of entering orders into a book (believe it or not) and my boss at the time wanted to keep the same system. I don't think this is ever likely to change.

The Access db was setup like this

Job No, Char, 6
Quote No, Char, 6

With Job No being the key field (indexed). However a job could be entered as a quote or a job and when the client accepted the quote, the quote then became a job. I got around this by making the Job No field = Quote No field while the job was at quote stage. When the quote became a job, the quote no in the Job no field was replaced by a proper job no - more difficult to explain than to program!

At quote stage:

I have two pushbuttons on the form next to these fields one called Auto Quote No the other Auto Job No so initially if we are quoting a Job we press the Auto Quote No button which fills Job No and Quote No with the next valid Quote No e.g.

Job No = "R1234"
Quote No = "R1234"

If the client accepts the quote the value in the Job No which is actually the Quote No is overwritten by the next Job No (by pressing the Auto Job No button, so assuming previous Job No was "2344"

Job No = "2345"
Quote No = "R1234" (provides cross ref. to original quote)

If the client placed an order direct the quote no was left blank (we just press the Auto Job No button)

Job No = "2345"
Quote No = ""

It looks messy but it works fine. Unfortunately, some other users have added prefixes of their own manually! It also does mean that the Job No has to be filtered to obtain the results I require.

I am sure if its possible in Access then Alpha must be able to do it. I was after a neat solution and tablemax seemed ideal! I thought the whole idea of having active link tables was so we could read the data from an original db, manipulate it and then use Alpha 5 to write it back to the original db, if we wanted?

[MikeC]

Re: Help with Tablemax function

Alastair,

This might work for you....use txt=table.external_record_content_get() to obtain the crlf list from your table. Check out the help file for the needed arguments for this function.

http://support.alphasoftware.com/alp...TENT_GET().htm

The following simply takes a crlf list, removes any records that have any of the characters in the exclude list (x_lst), then finds the largest record.

dim lst as c
lst=""
dim txt as C
txt=""
dim x_lst as c

''''''I used the linear comma list simply to reduce amount of script on the page with it then
''''''  converted to a crlf afterwards
x_lst=<<%a%
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z
%a%
x_lst=comma_to_crlf(x_lst)
txt=<<%list%
12345
R12345
MW2345
A12345
12346
12348
%list%


lst= filter_string_multi(txt,x_lst,chr(13)+chr(10),.t.)

?lst
= 12345
12346
12348
? *max(lst)
= 12348

[Alastair]

Re: Help with Tablemax function

Hi Mike,

Thanks for the advice, I will take a look at that command.

[ANGEL SANCHEZ]

Re: Help with Tablemax function

Perhaps, this can help.

I use the follow function:

FUNCTION UpdGim AS C (xField AS C )

dim xgim as P 
dim xNext as N 

xgim=table.open("gim")

ON ERROR GOTO error_handler
commit_flag = .t.
xgim.change_begin()
xNext = eval("xgim." + xField) + 1 
eval("xgim." + xField) = xNext
xgim.change_end(commit_flag)

UpdGim=xNext

END FUNCTION

error_handler:
commit_flag = .F.
RESUME NEXT

GIM is a Table with a field for every counter (i.e. documents), so you only have to synchronize ONCE the GIM with your transaction table and then call this function to get the new "max".
by the way, GIM works fine on dbf and on active links tables.

Hi Mike,

Thanks for the advice, I will take a look at that command.

[Alastair]

Re: Help with Tablemax function

Hi Angel,

Thanks for your reply and the function, however I don't understand the code at all! Can you provide some instruction on how I should use it?

Thanks,

Alastair

[ANGEL SANCHEZ]

Re: Help with Tablemax function

Hi there Alastair,

Gim is a table with only one record, but many numeric fields. One field per counter. So, you have to create a GIM Table with one record and one field at least, a field for your counter.

GIM
JOB_NO,N,10,0

You must start JOB_NO with your real max, only this time.

When you need to INSERT a new record with the next number in JOB_NO, you would not need to search it in your transaction table, just call the function, Like this:

newJOB_NO=UpdGIM("JOB_NO")

Luke!

Hi Angel,

Thanks for your reply and the function, however I don't understand the code at all! Can you provide some instruction on how I should use it?

Thanks,

Alastair

[Alastair]

Re: Help with Tablemax function

Hi Luke,

Thanks for your reply, but now I understand how your code works, I don't think it will work for me on this problem.

The active link table I am trying to set up is to read in a live value for Job No from an Access database, which is still in use. The Job No is being updated regularly in Access by other users during the day and I need the up to date live value of Job No copying from the Access db to the Alpha db.

Is setting up an active link table the right way to go about this or has anyone got a better idea?