When i save/make copy job, how can I check if the record is existed based on Company+ReportType+Auditor+Team+YearEndDate?
Hi Winnie,
You can simply use a comparison expression for this. Let say you have the following data structure;
Item.YearItem.CompanyIdItem.ReportTypeIdItem.AuditorItem.TeamItem.EndDate
You can then check the date via the following expression;
(Item.CompanyId <> NullIdentifier()andItem.ReportTypeId <> NullIdentifier()andItem.Auditor <> ""andItem.Team <> ""andItem.EndDate <> NullDate()) = true
This should help you validate the date input.
Hope this helps,
Vincent
Hi @Vincent Koning
May I know where should I put the above validation in checking, In Save button? Could you explain a bit more? Thanks.
You can do this at multiple places. I would do it at 2 where the first is optional if you don't need to worry about server load.
The starting course goes into more detail. Have you followed this one? Chapter "Building Web Forms" is the most relevant for your question.
Hi @Winnie Lam ,
Before saving or copying the data to DB, you should have to call an action to find-out the data is exist or not .. logic you need to create like this ...
Hope this can help you if not kindly share the OML
Kind regard.
@Sourabh sharma @Vincent Koning
Sorry for making the confuse, What I want is to check value of existing record , if there are same value of "Company+Auditor + Company Group+Team+report type + Year End" together for one record, then the system do not allow to save/copy.
Would you mind to make a simple example for my study base on this case which with checking value of multiple attributes? Thank you so much.
You can make a unique index in the entity on the attributes:
Company + ReportType + Auditor + Team + YearEndDate
If you insert a duplicate record an exception will be thrown.
That could not be what you want from an UX experience. You get a really bad error message back that you need to detect and transform into something the user will understand.
@Winnie Lam: I would do the following in the server action instead of the form validation for performance reasons and because there is nothing wrong with the values in the form per se,
In the CreateUpdate server action add an aggregate with the following filter (change it to your situation);
Entity.CompanyId = NewRecord.CompanyIdandEntity.ReportTypeId = NewRecord.RecordType()andEntity .Auditor = NewRecord.Auditor andEntity .Team = NewRecord.Team andEntity .EndDate = NewRecord.EndDate
If this query then returns one (1) or more records then you have a conflict. You can then exit the action by setting the correct output parameters to indicate a fault (don't throw an exception please). I always do the following: I set the IsError output to True and the ErrorMessage output to something that the user will understand. If you really want to go into overdrive you will also add the found record to the output (see why later).
The clients checks for the IsError message and if it appears if will show an notification with the ErrorMessage. This will inform the user that he will need to fix some things before he can continue. If you have added the found record to the output you can also provide this record as a "solution" and offer it to this instead (should the be a valid use-case of course).
I presume that you can act on that one with an exception handler. My answer was an extension to the answers already given to handle this stuff in the "frontend".As an "old-school" Oracle developer: don't trust the client, block it also in the database ;-)
My remark about the exception handler is that you get a system error that included references that a user does not understand. And it is all under the exception "Database" so what exactly has happened? Better to check the condition beforehand and give a meaningful error back.
And of course you can create a unique index as a safety catch to make sure that you don't get invalid data. But don't rely on that alone. Think about your poor poor users 😅
I totally agree ;-) Don't throw an ugly exception to the poor users ;-) Do both, handling in the client with nice "warnings" before it gets to the database, and the unique index as safety net :)
Hey Winnie,
Simplest solution for this problem is to check records in Table with this combination just before inserting into table. What you can do it, on your server action where you will be using Create action of Insert SQL query, just put an aggregate before that and in filter use your above 4 fields to check if there is any record with same combination, after aggregate put an if condition to check result, if its empty, insert record in table else output an user freindly error and display in screen.
can u make an oml example for my reference? Thanks.
Do not have enough time to create an OML, but this Ex will surely help you. Below is a create action where I placed an aggregate, in the aggregate, there are 3 filters to check if there any record exist with those combination:
Filters :
In If condition just check: AggregateResult.Empty , if it is then you can insert new record in Table.
Sanjay
Check same record is already present in DB before saving it into DB
Checking process:
1 . Build a server action with
Company+ReportType+Auditor+Team+YearEndDate. Input parameters for each variable
2. Output is already exist Boolean
3. Inside action make first aggregate with all sources you need
4 . Add this
Trim(toupper(company))=trim(toupper(entity.company))and
Report Type = entities.reporttype.attripute
And auditor if auditor is text value use trim(toupper(auditor))= trim(toupper(entity.auditor))
And like wise for team and year end date
filter and Max record property to 1
If aggregate is empty then is exist to false and not empty is exist to true
If is exist is true not allow to save unless show validation that the same record is already exist
thanks
Can you share oml with simple example for my study? Thanks.
Just wondering if I add an index for all those attribute as unique as below:
And once I have created duplicate record here , any way to edit the error msg here? Thanks.
You are almost there. To solve the ugly error message (which is an exception that has been bubbling up to the client because it is not handled anywhere before that) you need to create an action that will verify is the inserted data combination is not already available.
To do this create and Action called "VerifyIfUniqueCombinationExists" with an output "IsExist" and in this action you have an aggregate with the following filter (I don't know your exact datamodel so change it so something suitable)
Entity.CompanyId = NewRecord.CompanyIdandEntity.ReportTypeId = NewRecord.RecordType()andEntity.Auditor = NewRecord.Auditor andEntity.Team = NewRecord.Team andEntity.EndDate = NewRecord.EndDate
If a record is found you set IsExist to True.
When this action is done and returned back to just before the creation of the database record you insert an If between the action VerifyIfUniqueCombinationExists" and the "EntityCreateOrUpdate" action. In this action you check for the IsExist output parameter. If false you create the record. If true then you can return a nice message back to the end-user.
Since you said this is for a study I will not provide a OML since this would not help you understand the material. You should have enough to go on :).