Approach to achieve this context with a good performance?

I have a table Template and a table Posts that have some columns like:

Template: id, title, etc
Posts: id, title, description, TemplateId, etc

I have a LR_Posts in a web screen that shows the title and description of a post.
And I have a link there "Make Active" and when the user clicks in that link that post becomes active.

For that I have a screen action MarkPostAsActive that receives:
PostsId, Title, Description, TemplateId, IsActive
And it has a local variable "Post".

The action has an Assign:
Post.IsActive = True
Post.Id = PostId
Post.Title = Title
Post.Description = Description
Post.TemplateId = TemplateId

Then I have a CreateOrUpdatePost entity action that receives the Post local variable.

Then I have a ajax refresh and refresh the list records. This is working.

My doubt is, only one post can be active, so when the user clicks in "Active" for one post the other posts should
become with IsActive column as False, only the clicked Post should become with IsActive as True. Do you know how to properly achieve this in terms of performance, without query the db?

Rank: #70

Hi Jake,

For that scenario, you should do an advanced query that will inactivate the post(s) with is active = True.

Something like UPDATE {Post} SET {Post}.[IsActive] = 0 WHERE {Post}.[IsActive] = 1

You should run it before updating the new active one so you don't override it.

As a good practice for performance, bulk queries should be done using Advanced SQL rather than a query in a for each since it means one single query and database access rather than many queries.

Hope it helps.



Rank: #388

Hi Jake, 

Since you asked about performance, in addition to what João well suggested, as a good practice, one should just use the PostId as an input parameter for that MarkPostAsActive screen action instead of the entire record (Post.IsActive, Post.Id,Post.Title,Post.Description,Post.TemplateId).

Inside the MarkPostAsActive screen action, you can fetch all the other attributes through an aggregate, limiting its max records to 1. 

Best Regards