locked
Incremental "Job" number for new records (HTML+SL) RRS feed

  • Question

  • Hi all,

    First off, I just want to say thanks to the community. I've asked a bunch of "newbie" questions over the last year or so and I've always had great responses from you all. It's much appreciated!

    I'm trying to work out the best way to add a unique JobCode property to each record created in my Job table. I have HTML and SL clients accessing this. I don't need anything fancy, I just want to start with 20000 and increment by one for each new record. I've marked the property as unique in the table schema.

    I'm currently experimenting with a table called System which has one record for any settings I might need. I have a property called "LastCode" which I want to read just before committing the record +1 and make that records JobCode. I then want to write the new LastCode value back to that record.

    Current Issues with this approach are: I can retrieve the value with the following code from this previous request for help:

    myapp.AddEditJob.created = function (screen) {

    var LastJobCodeNum = 1 // There should only be one record in the System table. if (screen.Job.Id == undefined) { //Work out the JobCode - If LastCode is null we need to count the rows in the Job table and +1. myapp.activeDataWorkspace.Bookings.System_SingleOrDefault(LastJobCodeNum).execute() .then(function (result) { var record = result.results[0]; if (record != undefined) { var NewCode = (record.LastCode + 1).toString(); screen.Job.setJobCode(NewCode); } }); }

    }

    1.  For some reason this doesn't work if run in beforeApplyChanges which is where I would have thought would be a better place to trigger.
    2. I'm unsure of how to write the updated property back to the record in the System table.

    I've seen plenty of examples to create a new record, I've just not seen something (it's probably my poor search Fu) for updating a specific record.

    I've also not looked at the C# code for the SL client side of this either.

    I'd be interested how other people have addressed this? I did consider just trying to count the rows in the Job table and adding that to 20000 for example.

    As always, thanks in advance for any guidance :)

    Cheers,

    Adam

    Saturday, January 16, 2016 6:31 AM

Answers

  • Hi Adam,

    You want to update the record in System table when you insert a new entity which lead to the JobCode increase, is it right?

    For example, I have a Job table which contains the JobCode. When I add a job record into Job table, I will update my System table in Job_Inserted method as below code shows.

     partial void Jobs_Inserted(Job entity)
            {
                DataWorkspace dw = this.Application.CreateDataWorkspace();
                Job result = (from j in dw.ApplicationData.Jobs.OrderByDescending(job => job.JobCode) select j).FirstOrDefault();
                System system = dw.ApplicationData.Systems.FirstOrDefault();
                system.LatestJobCode = result.JobCode;
                dw.ApplicationData.SaveChanges();
    
            }

    Best Regards,
    Weiwei

    Monday, January 25, 2016 3:22 AM
    Moderator

All replies

  • Hi Adam,

    According to your description, you want to increase the JobCode with your rule of start from 2000, am I right?

    I think you can set this field as a compute field and then add your compute rule in JobCode_Compute method as below code shows. If misunderstanding, please feel free to let me know.

    partial void JobCode_Compute(ref string result)
    {
         // Set result to the desired field value
         this.DataWorkspace.ApplicationData.Customers.OrderByDescending(c => c.JobCode);
         Customer customer = this.DataWorkspace.ApplicationData.Customers.FirstOrDefault();
         if (customer != null)
         {
             result = (int.Parse(customer.JobCode) + 1).ToString();
         }
         else
         {
             result = "2000";
         }
    
    }

    Best Regards,
    Weiwei


    Monday, January 18, 2016 9:53 AM
    Moderator
  • Thanks Weiwei.

    I haven't tried this yet, but it's something for me to start with. I'll need to review how this will work for the HTML client but I can work that out. Is this searchable? I haven't used computed properties as they seemed a bit painful for the HTML client side of things and for totals (products services etc) the data wasn't available for reporting.

    How difficult is it to update a record in a different table? i.e. I know that the first (and only) record in the System table contains the property which I can read successfully. I just need to write data back to it.

    Cheers,
    Adam

    Tuesday, January 19, 2016 1:02 AM
  • Hi Adam,

    >>Is this searchable? I haven't used computed properties as they seemed a bit painful for the HTML client side of things and for totals (products services etc) the data wasn't available for reporting.

    The usage of Compute field is the same in HTML app and Desktop app, both of them add compute rule with C#/VB code in the _Cpmoute method. More information about computed column in LightSwitch, please searching on Google with key word "lightswitch computed column".

    >>How difficult is it to update a record in a different table?

    It is based on your business logical of your project. If you just want to update one of the records, it can be updated from Add/Edit screen, which can let us modify the entity you want to modify. If you want to modify another table when you update a entity, please add the update code in the entity's _Updated method.

    Best Regards,
    Weiwei

    Tuesday, January 19, 2016 3:16 AM
    Moderator
  • Hi Weiwei,

    Thanks for your reply. 

    I was under the impression that computed fields we're not the same in the LS and HTML clients. Michael's post here makes it look you need to write additional code.

    Also, the JobCode field will need to be searchable in the intrinsic database as customer will use it as a reference. I believe if it's computed it's effectively just visible in the client when viewed? This seems best placed for totals and such? 

    With regard to the response to updating fields, I know the basics. I should have written my question as:

    • How difficult is it to update a record in a different table programmatically when triggered from an action in a different table.  i.e. when a new record is created in the Jobs table, I would like to update the first record of the System table with the current LastJob number.

    So, my small application is nearly complete. The only missing element is cosmetics and this unique JobCode so I'd really welcome any ideas on how to solve this last hurdle!!

    Cheers,
    Adam

    Sunday, January 24, 2016 6:49 AM
  • Hi Adam,

    You want to update the record in System table when you insert a new entity which lead to the JobCode increase, is it right?

    For example, I have a Job table which contains the JobCode. When I add a job record into Job table, I will update my System table in Job_Inserted method as below code shows.

     partial void Jobs_Inserted(Job entity)
            {
                DataWorkspace dw = this.Application.CreateDataWorkspace();
                Job result = (from j in dw.ApplicationData.Jobs.OrderByDescending(job => job.JobCode) select j).FirstOrDefault();
                System system = dw.ApplicationData.Systems.FirstOrDefault();
                system.LatestJobCode = result.JobCode;
                dw.ApplicationData.SaveChanges();
    
            }

    Best Regards,
    Weiwei

    Monday, January 25, 2016 3:22 AM
    Moderator
  • use the _Inserting method to insert the ID as Weiwei suggested.

    partial void contractors_Inserting(contractor entity)
    {
        entity.contractorid = BaRiaService.GetNextID();
    }

    Then make a RIA-service that gets the ID for you. Below a table "bakeyctrs" is used and in this case the counter "ctrnr==1" and the incremented column keynr. BAKEYCTRS(ctrnr, keynr).
    I'm not sure that the below is good code but it has worked well.
    It would for sure be better to use some kind of Sql sequence within the GetNextId-code.
    At least we couldn't use Identity as we also have to create records with another tool not supporting Identity.
    Any comments appreciated! 

    public partial class BaRiaService : DomainService
    {
        #region Database connection
         ......
        #endregion
    
    
        public static object _lock = new object();
    
        public static int GetNextID()
        {
            lock (_lock)
            {
                int? nextKeynr = null;
                // Define a transaction scope for the operations.
                using (TransactionScope transaction = new TransactionScope())
                {
                    using (baliaData scopeContext = new baliaData(ConnectionString))
                    {
                        try
                        {
                            var bakey = scopeContext.bakeyctrs.Where(k => k.ctrnr == 1).SingleOrDefault();
                             if (bakey != null && bakey.keynr.HasValue)
                             {
                                bakey.keynr = bakey.keynr + 1;
                                scopeContext.SaveChanges();
                                nextKeynr = bakey.keynr.Value;
                             }
                             scopeContext.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
                             transaction.Complete();
                             scopeContext.AcceptAllChanges();
                          }
                          catch (Exception)
                          {
                            nextKeynr = null;
                          }
                     }
                 }
                 if (nextKeynr.HasValue) { return nextKeynr.Value; }
                 throw new Exception("Could not get next ID");
              }
            }
    
        }
    }

    Regards Roger.

    Monday, January 25, 2016 12:08 PM
  • Hi,

    Thanks. Yes this is the kind of approach I was looking for. I believe there is a small typo in your code. It should be Data rather than ApplicationData

                DataWorkspace dw = this.Application.CreateDataWorkspace();
                Job result = (from j in dw.Data.Jobs.OrderByDescending(Job => Job.JobCode) select j).FirstOrDefault();
                System system = dw.Data.System.FirstOrDefault();
                system.LastCode = result.JobCode;
                dw.Data.SaveChanges();

    This code "almost" works for me. I have code in the HTML client that increments the JobCode property on the Job table based on the LastCode property on the System table and this seems to function. The JobCode increments, and on saving the JobCode is correct for the Job.

    However, when this code block executes it updates the LastCode property in the one and only record in the System table to "AA".  

    The code makes sense to me. Does anyone have any thoughts on why?

    Cheers,

    Adam

    Monday, January 25, 2016 2:14 PM
  • Thanks Roger.

    I'll look at this approach if I can't resolve another way, but I was hoping to keep it simple.

    Cheers,

    Monday, January 25, 2016 2:15 PM
  • Hi Adam,

    I hope you noticed that having made the RIA-service, it's very simple.
    You can use the same counter for several tables.

    partial void contractors_Inserting(contractor entity)
    {
        entity.contractorid = BaRiaService.GetNextID();
    }


    partial void workers_Inserting(worker entity)
    {
        entity.workerid = BaRiaService.GetNextID();
    }

    Or if you need a sequence without jumps (1,2,3,4,...). Just use another counter than "ctrnr=1", ie. "ctrnr=2" that is used for that specific table. You then have to develop a new GetNextId2() that is using "ctrnr=2" or then make GetNextId more advanced taking a parameter with the name/number of the counter.
    You also have the flexibility to change how GetNextId works just in one place.

    Regards Roger.


    • Edited by RogerExp Tuesday, January 26, 2016 8:07 AM
    Tuesday, January 26, 2016 8:05 AM
  • Okay, so just to close this thread out. I reviewed my test data and I did indeed have records with alpha characters in them. Once I reworked this however the code stopped working for me. I now apepar to get null returned in the result variable and the save bombs out. I have worked around this for now by testing under the _inserting method. I'll keep testing for now and if I encounter timing issues with multiple users then I'llr e;view Roger's suggestion on RIA.

    Nothing against the RIA approach other than I haven't used this capability before so I've not looked at it yet.

    Thanks to all for your valuable input :)

    Monday, February 1, 2016 3:26 AM