What's the best way to keep track movement in Form

Applications Forms
#1

SC is best for create new employee with intuitive ‘new’ button
What if i need to keep track history/movement of an employee? What’s the best way to track in SC?

My initial ideal is have master table as employee and detail table as emp_history
Then create a master-detail app, where expecting the ‘new’ button in child app to track history/movement of employee.

E.g. “To change employee’s job title”
Steps:

  1. Click ‘new’ button
  2. Change ‘Job title’ field
  3. Save
  4. Done

But my challange now, after step 1, it doesn’t copy over existing fields to the ‘add new’ form, imagine my emp_history has 50 employee releated fields to track, i have to manually input each field in the ‘add new’ form.
What’s the way to auto copy existing fields into the ‘add new’ form’s field?

#2

It depends. You could work with a effective date construction where the actual situation is bound to a date. Other option is to create a clone table where you put the current record and then change, i.e. insert into cloneperson where (select * from …). Also you can use the audit-trail. Loads of options, but ‘the best’ is depending on what you feel best.

#3

I created a “clone” PHP button, but i get a blank page when i put my code as

{name_first} = “testing…”;

#4

Hi,
have a look at http://www.scriptcase.net/scriptcase-samples/tutoriais/form/form56.php this is something going in the same direction.

Also itsme3 had a similar question, may be it’ll help you as well: http://www.scriptcase.net/forum/showthread.php?6738

jsb

#5

Hi jsb, the method provided is to save into DB and alter later, right?

What if right after “add” button is clicked, before any it saves anything into db, is there a way to do it?
I need the latest history field to be copy over to the add form fields

Thanks for all the supports given

#6

Ok, here is the idea but you might want to adjust here and there to fit your needs.
If you have a grid as your detail, create a new one as a form -> editable grid form.
Remove all but the navigation buttons and create a new ‘new history’ PHP button.
PHP Code:
sc_redir(add_new_form,v_employee_id=[employee_id]); //I assume the employee_id as global variable available.

Create (or copy an existing one) a single record form based on the history table (add_new_form). Put all fields on the form and hide those you don’t want to be seen.
Remove all buttons but First and Exit from the toolbar.

Go to SQL Settings
Where Condition: employee_id = [v_employee_id]
Order By: history_date DESC

onScriptInit:
sc_btn_display(‘first’,‘off’);

onLoad:
{primary_key} = null;

Create a Javascript button (i.e. js_insert).
JS Code:
nm_atualiza(‘incluir’);
window.location.href = ‘…/detail_app/detail_app.php’;

I think that should do it.

jsb

#7

hi guys, interesting information and ideas

I just wanna share my way of doing this when i needed it a while ago

1- clone the main table you you want to keep track (e.g. mysql operations > copy table) name e.g. table_history
2- very important: add a new field in your new table name it e.g. updateid…make it primary and AI instead of your copied one of the original table… this is not to mix with the primary key of the original table that you are getting the data from…
— moreover, you can exclude the the primary key field of the original table if you like… or rename it to “id_original_table”…
3- add one more field as timestamp - default: update datetime
4- in your original form > events > on afterupdate > the right side pre-made codes “insert a record on another table”

basically you will need something like this:


	
$insert_table  = 'table_history';
$insert_fields = array( 
	  'id_in_original_table' => "'{id}'", \\ this is your primary key in the original table... is not needed there in history i think... 
	    'field1' => "'{field1}'",
	    'field2' => "'{field2}'",
	    'field3' => "'{field3}'",
	    'field4' => "'{field4}'",
	    'field5' => "'{field5}'",
	    'user' => "'[usr_name]'",
 );

$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';

sc_exec_sql($insert_sql);

fields on the left should be from the history table, and from the right are your fields of the original table {}

5- make some updates, see if data all goes ok and/or adjust as per your need
6- create new grid based on the history table to customize to filter the history you need and make it start by search for example to filter the huge data if any

looks like a log module, but that one registers everything, this one can be adjusted to the need

Cheers
Mike

#8

hi jsb, what’s following code trying to do?

onLoad:
{primary_key} = null;

Create a Javascript button (i.e. js_insert).
JS Code:
nm_atualiza(‘incluir’);
window.location.href = ‘…/detail_app/detail_app.php’;

Thanks itsme3 as well, i will try to see if that’s suitable for my scenario.

also, what’s the different between onScriptInit and onLoad?

#9

The code in onScriptInit is executed when the application is loaded, basically just before the sql statement is executed.
onLoad runs after onScrIptinit when a record is loaded. http://www.scriptcase.net/docs/en_us/v8/manual_mp.htm#form-applications/form/events/events

{primary_key} = null;
I assumed you have set the primary key of your ‘history’ table to autoincrement.
This statement makes sure the record gets a new id when inserted otherwise the db would abort the operation because of key violation.

nm_atualiza(‘incluir’); //inserts the record shown in the form into the ‘history’ table.
window.location.href = ‘…/detail_app/detail_app.php’; //redirects to the detail grid.

jsb

#10

where app to put following code?

nm_atualiza(‘incluir’); //inserts the record shown in the form into the ‘history’ table.
window.location.href = ‘…/detail_app/detail_app.php’; //redirects to the detail grid.

When to trigger it? coz i have my add history app with the logic above in “js_insert” button
http://sc.astudion.com/sc8/app/VirtualDesk/PersonPersonalityAdd/PersonPersonalityAdd.php

But when i clicked on it, it jz redirect to itself without insert anything

My keys are

  • “Hcm Person Person Id”
  • “Effective”
#11

i also tried another method, create a PHP “Clone” button with following code

sc_exec_sql(“INSERT INTO hcm_person_detail (effective, gender, birthdate, marital_status, nationality, race, religion, highest_edu, hcm_person_person_id, name_first, name_middle, name_last, name_display) VALUES (‘2014-09-25’, ‘M’, null, ‘S’, ‘’, ‘CHN’, ‘FT’, ‘SPM’, 1, ‘www’, ‘adb’, ‘’, ‘’)”);
sc_commit_trans();

sc_redir(PersonPersonality);

it managed to insert, redirect to my app, but strange thing is, the navigation doesn’t ‘plus one’ for this new row. (i am using Single record form)

#12

By looking at your table structure I strongly suggest that you first create another field in your hcm_person_detail table, i.e. detail_id, integer, autoincrement (this field is your primary key)
otherwise it won’t work as expected.
This form is only mend to be used for editing a NEW record (with all fields copied from an existing record) before it is inserted into the table.

Here it goes (with minor changes):
You have an application (let’s say person_form) which shows the person you want to edit and insert the respective details as a new record in the details table.
On this application you create a PHP button (‘New Details’)with the code: sc_redir(add_new_detail_form,v_hcm_person_person_id={hcm_person_person_id});

ADD_NEW_DETAIL_FORM single record based on hcm_person_detail (no buttons except Exit, First and the custom Javascript button).

SQL Settings
Where Condition: hcm_person_person_id = [v_hcm_person_person_id]
Order By: detail DESC

onLoad:
sc_btn_display(‘first’,‘off’);
{detail_id} = null; //see first line of the post. This forces the db to create a new id, otherwise you have to calculate it by yourself.

onAfterInsert:
sc_commit_trans();
sc_redir(person_form,hcm_person_id={hcm_person_person_id});

JS button:
nm_atualiza(‘incluir’);

When this form is loaded, all fields are filled with the latest details. You can make all necessary changes and by clicking your custom js button
it is inserted as a new record in the details table and you are redirected to the person_form app.

jsb

#13

[QUOTE=jsbinca;27607]By looking at your table structure I strongly suggest that you first create another field in your hcm_person_detail table, i.e. detail_id, integer, autoincrement (this field is your primary key)
jsb[/QUOTE]

this is what i highlighted in my previous post when explained other way that i used, and emphasized that this is very important, because it happened with me, things messed up because the history table should has its own records count…

#14

I followed the steps and able to insert and redirect back to my person app
http://sc.astudion.com/sc8/app/VirtualDesk/PersonPersonality/

But it doesn’t reflect the additional row at once in my navigation. I need to click browser refresh and it reflect the latest row. Is it a bug or I missed out something?

#15

hi all,

this is my half-complete prove of concept
http://sc.astudion.com/sc8/app/VirtualDesk/PersonMaster/

as u can see, it added the histroy record by click on “Add History” button. it able to save to DB.
But yet… the pagination doesn’t update when it back from insert (clicked js_insert).

When i click on Refresh button and the pagination is updated, how could i skip the ‘manual refresh’ step?

Thanks and i am almost there!

Thanks for all the support

#16

hi weilies,
did you see this video?
http://www.youtube.com/watch?v=otYeUWjpmus

exactly at time 6:45 mins. may help.

#17

hi itsme3, my problem above is the pagination not updated (<1, 2, 3… >) when there is a new row added
not to update a master field.

E.g.
Currently i have 2 detail rows (it shows < 1, 2 >)
When i added a detail and it redirect back, it’s still showing < 1, 2 > where i expect it to shows < 1, 2, 3 >
hope it’s clear for my explanation :slight_smile:

You can play around with my app via link above

i realized when i removed some hardcode and the app doesn’t work properly.
I have my
Person 1 - with no effective (date key) 29 Sep
Person 2 - with effective 29 Sep

When i try to insert 29 Sep for Person 1, it complain key exist

#18

Hi jsb, what I didn’t followed, is to set PM = null
My keys for the history table are
Person id, which is the FK of master person table
Effective, a date key field which I always set as today and its unique bcoz no row inserted for today yet.

Correct me if I am wrong, by setting both field above, I don’t need to create extra PK field to auto increment, right?

#19

hi

yes dear i understand, i guess it is the same idea, you need some sort of ajax to make the update, that’s why i posted that link to have a look

actually i didn’t work on such scenario before, that is why i am not sure i can help in this, but i am sure guys here will provide you with your suitable solution, just be patient a little they might didn’t find enough time yet :slight_smile:

btw: auto increment for “any” table is almost a must

#20

hi itsme3, i worked from mysql > Oracel DB > mysql again
So, my mindset changed a bit, auto-increment field is not required in oracle as long as the unique keys are identifiied. i assumed the same concept applied to mysql and plz correct me if i am wrong.

E.g.
Master Person >> PK=person_id
Person History tracking >> PKs = person_id + effective(date field)

Does SC required something like below?
Person History tracking >> PKs = person_id + effective(date field) + change_id(auto_increment)

Coz i tried jz now to add the auto_increment field, i got the same erroro “row already exist”

Hope to get help from gurus