Getting the MAX record

Classic Servoy
1

Hi all,

I have the following tables:
1.ID
2.Person_In_Househols

I created a form based on Person_in_Household table.
Based on the relations for ID table I added the ID fields within Person_in_Household form.

For the ID table I created aggregations to get me the MAX number of records Get_Max_SUBJ_NUM_ID.

In Person_in_Household form I created a Button that On actions calls Method called New Record .
In the New Record method have:

//Clear the form
controller.loadOmittedRecords();
//Show all the records
controller.loadAllRecords();
// Create a new record
controller.newRecord();
var SB = ++ persons_in_household_to_id.subj_num
//if subj_num is null and status is new record
if (persons_in_household_to_id.subj_num == null && “controller.newRecord();” )
//Get MAX Subj_num record and add 1 into it
{
// Get the next subj_num
persons_in_household_to_id.subj_num = SB
}

But when I click on new record button no new subj_num get inserted into the Subj_num field.

Please let me know why this method is not working.

Thanks in advance,

Abrahim

2

Hi Abrahim,

Can’t say that I understand what you are doing here.

Could you explain it for a simpleton like me to comprehend.

What is the role of the ID table and how is it related to the persons_in_household table ?

Are you trying to organise a family unit here !?

Cheers
Harry

3

first please post the tables and the relations you are using, because that isn’t clear at all.

second what are you doing here?

var SB = ++ persons_in_household_to_id.subj_num

what does that ++ do there??
and:

if (persons_in_household_to_id.subj_num == null && "controller.newRecord();" )

What do you thing “controller.newRecord;” between quotes does??

4

Hi Johan,

Abrahim has been trying to explain things off list to me and here is a summary of what is going on :

The ID table is used to ‘roll your own’ serial ID numbers.

Thus each time a new record is created in the ‘person_in_household’ table, the method goes and gets the next serial ID from the ID table by using the ‘++’ and then place it into a variable called ‘SB’

Then it creates a new record and places the ID into the ‘Subj_Num’ field in the ‘Person_in_household’ table.

The method incorrectly tries to use the ‘controller.newRecord()’ in the ‘If’ statement as a boolean condition !!

There is a calculated field in the ID table which resolves the max() value in the ‘Person_in_household’ table and this is how Abrahim expects to keep the ID up to date !

Hope this outlines what he is trying to do !

I did ask why he did not use the aute enter serial properties of Servoy or the database to which I received this reply :

Here are few reasons why I’m not using The Servoy Sequence function or Backend DB to incremented the Subj_num:

  1. The Servoy function is not working the way we wanted.
  2. The Database part is not possible, because the table has been populated with more then 10000 records already.
  3. The old Database interface we have "Ms Excel via VB) used to do this function for us.

That’s the story so far.

Now I’ll think a little before responding further !

Harry

5

ahh ok
i was a bit confused because of this:

var SB = ++ persons_in_household_to_id.subj_num
if (persons_in_household_to_id.subj_num == null && “controller.newRecord();” )
{
persons_in_household_to_id.subj_num = SB
}

because if ‘persons_in_household_to_id.subj_num’ == null
then SB will also be null. Because right above the if you do this:

var SB = ++ persons_in_household_to_id.subj_num

but if the if just below this line is true then SB will also be nothing.

and “controller.newRecord();” can’t be used this way.. I still don’t get what you are testing here. You do a fewlines above controller.newRecord() so the newRecord is being created..

I also still don’t exactly get what the relation person_in_household_to_id looks like.
Because if you make a newRecord() (what yo udo there. Is then the value ID that is in the person_in_household table filled in with a value so that the relation to the ID table can be looked up?

i also don’t know where what values are looked up for id’s and which columns are pk’s

But i also want to warn you!! please becarefull with own sequences that you handle on the clientside!! This is dangerous!! Because what happens if 2 clients do make a newRecord in about the same time?? Then you will get 2 rows with the same id.. Sequences should be handled by the server or database.

6

Johan, I too have sequences handled by the client!
Because, a supervisor, have to set the initial value once a while.

I do this as following:
main_to_main.invoiceid = main_to_main.invoiceid + 1
controller.saveData()

Than the change of the same invoiceid is zero? or am I wrong?

7

why do you think that is zero?
If on aprox. the exact same time that method is executed then both clients read the value X from main_to_main.invoiceid
and then they are both setting x+1 to the new one.
If you have an unique constraint on that one. The the one which is just a bit slower will get an error ofcourse.

Of course many do a select Max() and use that one as the next value.. but this is not very save.

In servoy what you can do is a special lock table. (what akalehzan is doing i believe)
Where there is just one row for a specific table holds the sequence top.
Then you can create a global relation where the left side is a global where you put in the table (sequence name) and the right side is de id table (which has 2 columns, sequence_name and seqeunce)

This will return then then that specific row.
Then use the lock record mechanism of servoy to lock that row.
Read the value.
Increment it.
save it
remove the lock.

Then you have a value that is unique.