Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      I created a "clone" PHP button, but i get a blank page when i put my code as

      {name_first} = "testing...";

      Comment


      • #4
        Hi,
        have a look at http://www.scriptcase.net/scriptcase...orm/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

        Comment


        • #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
          Last edited by weilies; 09-24-2014, 08:56 PM.

          Comment


          • #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

            Comment


            • #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:

              Code:
              	
              $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

              Comment


              • #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?
                Last edited by weilies; 09-25-2014, 02:13 AM.

                Comment


                • #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.../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

                  Comment


                  • #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/Virtu...onalityAdd.php

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

                    My keys are
                    - "Hcm Person Person Id"
                    - "Effective"

                    Comment


                    • #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)

                      Comment


                      • #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_i d={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_per son_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

                        Comment


                        • #13
                          Originally posted by jsbinca View Post
                          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
                          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....

                          Comment


                          • #14
                            I followed the steps and able to insert and redirect back to my person app
                            http://sc.astudion.com/sc8/app/Virtu...onPersonality/

                            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?

                            Comment


                            • #15
                              hi all,

                              this is my half-complete prove of concept
                              http://sc.astudion.com/sc8/app/Virtu.../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

                              Comment

                              Working...
                              X