Need to update the record count in software package table

[suuriyas]

HI Community,

I have a requirement, we have schedule job which run daily in software instances table if record is not updated for more than 7 days then install on field is made empty. now in software package table the count needs to be reflected.

For example there is" x " software record in software instances table and there are 12 records and 3 of them are not updated for more than 7 days so for 3 records  install on is made empty now in the software package table the install count should be display as 9 instead of 12.

How can we achieve this?

Thanks in advance

[J Siva]

@suuriyasadd the installed on field condition in the encoded query. 

Ex:

software=d3d2a1be87a65250da3ecbbc8bbb3500^installed_onRELATIVEGT@dayofweek@ago@7

[suuriyas]

HI @J Siva ,

I tried it works but all other records in software package table is also getting updated.

For example in script in encoded query i have enter only for one particular product named X but in software package table the count is getting updated in all other records only in X record i need to update the count but it is getting updated in all others as well.

And in some case the calculation is not working correctly for example: Zoom Meetings 6.3 is the product name in software instances table i have total 721 records and install on empty is 366 and not empty is 355 so in software package table it should update as 355 but it is updated as 302

I think we need to use json for this if im not wrong

[J Siva]

@suuriyas 

Since you've used while(prod.next()), the script iterates through the entire table, resulting in the same value being updated across all entries.

Now update the encoded query as below, 

"installed_onRELATIVEGT@dayofweek@ago@7^software="+prod.sys_id

Our encoded condition verifies whether "installed_on" falls within the last seven days. That's what your initial requirement. And there's nothing to do with JSON.

[suuriyas]

HI @J Siva ,

Thanks for the explanation.

But mine is installed on is not empty so i have changed to installed_on!=NULL in encoded query.

I tried this 

'software=b4245c15eb551210b2bff25e1bd0cd7a^installed_on!=NULL' it works updated the exact number i want but it is updating in all the records in package table so i have added this

'software=b4245c15eb551210b2bff25e1bd0cd7a^installed_on!=NULL^software=+prod.sys_id' but it is updating as 0 in all the records in package table

and other issue here is we have 4000 plus different product name in software table how can we update all?

for testing purpose i have pick one product name and added in encoded query and tested it but actual requirement is we need to update for all different records and these needs to be updated correctly on each records in software package table.

How can we achieve this?

Any suggestions Please

[J Siva]

@suuriyas 
Modify your encoded query as below,

installed_on!=NULL^software=+prod.sys_id

The above query will meet all your requirements.