Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Run an action on recently added records to a table

Hi,

I have an INSERT SQL query which is contained within a LOOP action.  When the LOOP is completed I need to ask the user if they want to run a task relating to the new records which were just inserted.

My plan at the moment is to have a new field added to the table which the data is being inserted and name it NEW (yes/no).  When new records are inserted, the insert query will assign them YES.  At the end of the process when the user is asked if they want to see/print the newly inserted records I can then run a select query based on that table where the NEW field = true/1.  Once this has run I will set all TRUE fields to FALSE.

Does this method make sense?  Am I going about this totally wrong?  Is there a better and cleaner way of approaching this?  Really happy to see suggestions of simpler coding.

Thanks
Anthony
Avatar of skullnobrains
skullnobrains

it does make sense for a single user.

it does not if you expect multiple users to use the same process simultaneously.
if each user needs to see all that is new regardless who inserted the rows, you can store an increment or date in your field and mark in a separate table that the user has reviewed rows up to some point.
if each user needs to follow this same process for the rows he has been working on, you can store the name of the user that needs to do the review in your extra field and null otherwise.

anything more complex will require an extra table.

if you have an existing id which is likely, it might be more cost-effective in terms of used space to create a separate table to store the last reviewed row number rather than add a field to each rows in the table
Yes, Let your "task relating to the new records" perform an update of the field New.

/gustav
Avatar of anthonytr

ASKER

Thanks for the input!

In a multi user environment I could add two fields: NEW and USER.  On INSERT, I can check YES for NEW and add the current user as well.  That way I only update the items added for the specific user who has just added them.  Adding the user is also a good way I keeping track on who actually added the record.
When doing batch processing, I use a BatchID.  I have a table that tracks the batches so the first step is to insert a row into the batch table logging the batch.  It includes whatever information might be useful later.  At a minimum, it contains the create date.  When the batch is an import, I also log the full file name that was imported.   Then as each record is inserted, it gets tagged with the batchID.  That way, you never have to update the record and you can actually look at old batches.  The new/old flag doesn't do anything useful and won't work at all in a multi-user environment.  Adding the user name is more helpful but it still has to be removed at the end.  The batchID stays in the permanent record.
Hi Pat,

Could you explain your method a bit more?  In my example, I have a table called tblLabels.  This table is used to record Item Barcode Lables being generated for asset tracking.  A user can specify a customer name and quantity of labels to print.  The action to INSERT them into the table runs and the user is asked if they want to print/view the new records.  How would your method fit into this?

Anthony
if you want each user to review his own lines, store the user's name in the extra field, and nullify the field once the rows have been reviewed.

if you want something different, you need to be precise regarding your requirements : you may want each person using the app to see the record that were previously inserted in the same session for example and the system to forget what was done afterwards or if the user disconnects before reviewing, for example. in that case, you probably should just keep the list of ids in a variable and not bother touching the db at all.

on the other hand, you may want some generic tracking in which case you'd need to store an incremental id or possibly a timestamp and the user's name with the row, and keep track in a separate table of the last id each user already reviewed.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The other suggested methods including your own, give you one shot at printing the batch

the yes/no field suggested by the author does not suffer such shortcomings : you don't update the field unless the user did review stuff. using a (possibly existing) autoincrement col and storing the point until which stuff have been reviewed in a separate table does not either. but anyway we don't know what is expected yet.
Is this for MS Access or MS SQL Server?  You have included both so it is unclear what is your database.
The BE is SQL Sever and FE is MS Access
The backend does not really matter. You need to make clear your needs regarding older records. If you only need a one time proposal regarding the records added in the same session it is likely easier to keep track of the records in access but per session batch ids would work as well and help with tracking
Hi,

Thanks for all the suggestions received.  Just to clarify, when new records are added by a user into the table, the user is asked if they want to see the new items which have just been added and they have the ability to print details of the new records.  That is why I need to track what has just been added to the table.  There is no requirement to be able to 're-open' the batch at a later point of time.  The user is given a simple YES or NO question.  If they don't want to review what has just been added - that is it.

Thanks
There is no requirement to be able to 're-open' the batch at a later point of time.  The user is given a simple YES or NO question.  If they don't want to review what has just been added - that is it.

then you should probably remember the added items in access. there is no point in modifying the db.

but if you need to, generate or use a per session id, insert records with the id and all should work as expected. compared to your initial yes/no solution this is similar but will work if multiple users use the system at the same time.