none
Mail when value of field < or > paramater% RRS feed

  • Question

  • Hello,

    I've a question about a report (or message) when the value in a sql table changed under or above a value.

    Detail information : we have a table T with field F. The value of field F = 10

    When someone, in the program (ex GUI ERP), the value, by mistake, changed to 15 then I want to receive a mail/message/report.

    So, I must configure the under and above parameters. (ex + or - 10% of the value)

    More detail :

    In the GUI of or ERP program a users changed a purchase price. But he do a 'typo'. Instead of 10,15 euro the user entered  101,5 euro. Or we calculate purchase price * field A / field B must max +/- 30 % of sales price. If not also a message.

    Example : field A + (B * C) / D and then check with sales price (the paramter 30 %)

    So if someone changed one of these fields (A, B, C or D) and the new total is + or - 30% then a report.

    This mean someone entered a wrong value.

    The question is : 

    - Is it possible to configure this in Reporting Services ?

    - I read you can do it by DML but other says to enter my question in the Admin / T-sql forum.

    - If yes houw can you do that ?

    Thanks and regards.


    • Edited by Brussel100 Monday, June 17, 2019 3:10 PM
    Friday, June 14, 2019 6:48 AM

All replies

  • Good day,

    This forum is for Transact-SQL - meaning discussions related to writing (code) queries in Transact-SQL.

    Do you want me/us to move your question to the SSRS forum?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, June 14, 2019 7:04 AM
    Moderator
  • Sorry, yes you may move the question to SSRS forum.

    Can you post also the link ?

    Friday, June 14, 2019 8:23 AM
  • Sorry, they ask me to post the question in this forum.
    I wasn't aware the question is double posted.

    Friday, June 14, 2019 8:24 AM
  • I am not sure where you stand now (what is the status) and if we should marge this thread to the other one or not

    The original question is clearly an SSRS question, but in the other discussion you came to a pint where you discusses about manually writing query.

    The discussion regarding the query fit to this forum
    The discussion regarding the original question related to the SSRS forum

    Dilemma😃
    What now...
    Which thread should we continue? Which is the updated and what is the status
    someone need to sync the thread to understand what is still open

    THIS IS THE PROBLEM OF HAVING MULTIPLE THREADS!
    It is very hard to follow where we are and to sync between the threads


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, June 14, 2019 8:55 AM
    Moderator
  • I've a question and I've posted in a SQL forum. Because I thaught it was a reporting services question.

    With the hope some can help me.

    But one of the answers , from a forum guy, told me that you can do it with DML.

    And give me some code.
    I try it but didn't work.
    Then another forum guy told me it's SSRS question. You must put it there.

    So I did.

    That I now have two treats was not the intention.

    If it is possible then I delete the question in the other problem. No problem.

    The  status :

    The problem I descript in this treat. I want, if possible, receive a mail when a user changed (or new input) a value by ERP GUI and the value or the sum (calculate) is higher / lower then a value (ex sales value)

    Thx for understanding.

    PS : I've deleted the other question.
    So hopefully somebody can help me.

    • Edited by Brussel100 Friday, June 14, 2019 11:24 AM
    Friday, June 14, 2019 11:17 AM
  • I've a question and I've posted in a SQL forum. Because I thaught it was a reporting services question.

    With the hope some can help me.

    But one of the answers , from a forum guy, told me that you can do it with DML.

    And give me some code.
    I try it but didn't work.
    Then another forum guy told me it's SSRS question. You must put it there.

    So I did.

    That I now have two treats was not the intention.

    If it is possible then I delete the question in the other problem. No problem.

    The  status :

    The problem I descript in this treat. I want, if possible, receive a mail when a user changed (or new input) a value by ERP GUI and the value or the sum (calculate) is higher / lower then a value (ex sales value)

    Thx for understanding.

    PS : I've deleted the other question.
    So hopefully somebody can help me.

    Hi Brussel,

    Thanks for your reply.

    Please use some sample table with data to support your description, and based on which condition, the email should be sent? What is the content of that email message?

    Without these attached information, we couldn't exactly know how the work goes on?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 7:29 AM
    Moderator
  • Hi Will,

    The question is very simple but I think the solution is the challenge :-)

    A very easy example. See printscreen.

    You have a ERP GUI screen, with SQL tables, and the user changed a value of a field.
    In this printscreen the cost price. Then when the new value is < or > then 30% a simple mail must be sent to a user. Let say ict@exchange.com (we use Exchange as mail server)

    So an example : TO user XXXX, Subject text, body text is enough.

    Or when the calculation of some fields < or > is then another field.
    Example : cost price + transport + packing cost - discount is < or > 40% off the sales price.

    As an example :

    Table : Articles

    Field : costprice, sales price, packing, transport and discount

    For me it's importent how to start. How to configure stuff like this.
    And then I can find/search on the internet for more solutions.
    But now I have no clue how to start / configure ?

    

    Thx for understanding

    Monday, June 17, 2019 12:12 PM
  • Hi Brussel,

    Thanks for your reply.

    Per your further detailed clarification, the requirement belongs to business rules. At the technical level, you need to send the message to specific user, so could only use trigger to achieve that in SQL Server.

    But in my opinion, such requirement is more suitable to be achieved in front-end application. Also, since your requirement is more clear than before, but it is far away from taking the expression into action. As you know, business rules must be more specific, it needs to communicate many times. Such solution for this type of question couldn't be completely matched in the internet, also in the forum. Because it is the customize requirement.

    If possible, please use some sample data to describe your requirement, otherwise, it is hard to help you achieve your own thought. Further more, it is beyond the range of our support.

    Or you could ask a local development engineer to develop such requirement for you, I think it would be more efficient to help you solve your problem. Just putting more effort on the forum couldn't completely solve your problem, maybe some detailed things are missed, or some logical conditions haven't been considered before, what we could do is to lead you to right direction at the technical direction.

    Thanks for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 18, 2019 7:41 AM
    Moderator
  • Hi Will,

    Thx for reply.

    I understand a local system engineer of ask the ERP partner to configure this. But ERP system and change is not easy and cost a lot of money.
    Is this methode also possible ?

    But the mail is not configured. So I'm scared to do it. I also read that you can copy the record to another file.

    But what is the code ?

    You need to create trigger using CREATE TRIGGER statement

    like

    CREATE TRIGGER TriggerName

    ON TableName

    AFTER UPDATE

    AS

    BEGIN

    IF EXISTS(

    SELECT 1

    FROM INSERTED i

    JOIN DELETED d

    ON d.PK = i.PK

    WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01

    )

    EXEC sp_sent_dbmail ....

    END

    Here PK would be the primary key of the table

    And sp_sent_dbmail is the procedure used for sending email notifications

    You've to enable database mail feature for using this

    see

    https://www.sqlshack.com/configure-database-mail-sql-server/

    Tuesday, June 18, 2019 12:29 PM
  • Hi Will,

    Thx for reply.

    I understand a local system engineer of ask the ERP partner to configure this. But ERP system and change is not easy and cost a lot of money.
    Is this methode also possible ?

    But the mail is not configured. So I'm scared to do it. I also read that you can copy the record to another file.

    But what is the code ?

    You need to create trigger using CREATE TRIGGER statement

    like

    CREATE TRIGGER TriggerName

    ON TableName

    AFTER UPDATE

    AS

    BEGIN

    IF EXISTS(

    SELECT 1

    FROM INSERTED i

    JOIN DELETED d

    ON d.PK = i.PK

    WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01

    )

    EXEC sp_sent_dbmail ....

    END

    Here PK would be the primary key of the table

    And sp_sent_dbmail is the procedure used for sending email notifications

    You've to enable database mail feature for using this

    see

    https://www.sqlshack.com/configure-database-mail-sql-server/

    Yes, the technical direction has no problem.

    The rest of the work is to maintain your business rules based on this direction.

    Good luck!

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 8:26 AM
    Moderator