0

I have created an Heroku app to be able to retrieve records (Contracts) from Salesforce and display them on my Heroku app. But I am struggling to write the correct request on my node.js code. Basically when pressing "Retrieve Contracts", I would like to get a list of contracts as shown on the pic.

enter image description here

But in my case, I get an Error anytime I press "Retrieve Contracts" {"error":"relation "contract" does not exist"}

enter image description here

To get the records from Salesforce I'm using "Express.js" and "app.get" (here is a snippet)

    app.get('/getContracts', function(req, res) {

    pg.connect(process.env.DATABASE_URL, function (err, conn, done) {

        // watch for any connect issues
        if (err) {
            console.log(err);
            return;
        }

        conn.query('SELECT Name,Product__c FROM Contract WHERE Product__c != null',
        function(err, result) {
            console.log(result)
            if (err) {
                res.status(400).json({error: err.message});
            }
            else {
                // Need to display 'Success!'
                res.json(result);
            }
        });
        
    });
});

here is the full code:

server.js:

var express = require('express');
var bodyParser = require('body-parser');
var pg = require('pg');

var app = express();

app.set('port', process.env.PORT || 3000);

app.use(express.static('public'));
app.use(bodyParser.json());

app.post('/update', function(req, res) {
    pg.connect(process.env.DATABASE_URL, function (err, conn, done) {
        // watch for any connect issues
        if (err) console.log(err);
        conn.query(
            'UPDATE salesforce.Contact SET Phone = $1, HomePhone = $1, MobilePhone = $1 WHERE LOWER(FirstName) = LOWER($2) AND LOWER(LastName) = LOWER($3) AND LOWER(Email) = LOWER($4)',
            [req.body.phone.trim(), req.body.firstName.trim(), req.body.lastName.trim(), req.body.email.trim()],
            function(err, result) {
                if (err != null || result.rowCount == 0) {
                  conn.query('INSERT INTO salesforce.Contact (Phone, MobilePhone, FirstName, LastName, Email) VALUES ($1, $2, $3, $4, $5)',
                  [req.body.phone.trim(), req.body.phone.trim(), req.body.firstName.trim(), req.body.lastName.trim(), req.body.email.trim()],
                  function(err, result) {
                    done();
                    if (err) {
                        res.status(400).json({error: err.message});
                    }
                    else {
                        // this will still cause jquery to display 'Record updated!'
                        // eventhough it was inserted
                        res.json(result);
                    }
                  });
                }
                else {
                    done();
                    res.json(result);
                }
            }
        );
    });
});

app.get('/getContracts', function(req, res) {

    pg.connect(process.env.DATABASE_URL, function (err, conn, done) {

        // watch for any connect issues
        if (err) {
            console.log(err);
            return;
        }

        conn.query('SELECT Name,Product__c FROM Contract WHERE Product__c != null',
        function(err, result) {
            console.log(result)
            if (err) {
                res.status(400).json({error: err.message});
            }
            else {
                // Need to display 'Success!'
                res.json(result);
            }
        });
        
    });
});

app.listen(app.get('port'), function () {
    console.log('Express server listening on port ' + app.get('port'));
});

index html:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>CRM AXG</title>
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
    <script src="//code.jquery.com/jquery-1.11.3.min.js"></script>
    <style>
        body {
            padding-top: 60px;
        }
    </style>
    <script>
        $(function() {
            $("#phoneChangerForm").submit(function(event) {
                event.preventDefault();

                var errorMessage = $("#errorMessage");
                var error = $("#error");
                error.hide();

                $("#message").hide();

                var firstName = $("#firstName").val();
                var lastName = $("#lastName").val();
                var email = $("#email").val();
                var phone = $("#phone").val();

                if (firstName.length == 0 || lastName.length == 0 || email.length == 0 || phone.length == 0) {
                    errorMessage.text("All of the fields are required.");
                    error.show();
                }
                else {
                    $.ajax({
                        url: event.target.action,
                        method: event.target.method,
                        data: JSON.stringify({
                            firstName: firstName,
                            lastName: lastName,
                            email: email,
                            phone: phone
                        }),
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function(data) {
                            $("#firstName").val("");
                            $("#lastName").val("");
                            $("#email").val("");
                            $("#phone").val("");
                            $("#messageMessage").text("Record updated!");
                            $("#message").show();
                        },
                        error: function(err) {
                            errorMessage.text(err.responseJSON.error);
                            error.show();
                        }
                    })
                }
            });
        });

    </script>

    <script>
        $(function() {
            $("#getContractForm").submit(function(event) {
                event.preventDefault();

                var errorMessage = $("#errorMessage");
                var error = $("#error");
                error.hide();

                $("#message").hide();


                $.ajax({
                    url: event.target.action,
                    method: event.target.method,
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function(data) {
                        console.log(data);
                        $("#name").val("");
                        $("#product__c").val("");
                        $("#messageMessage").text("Success!");
                        $("#message").show();
                    },
                    error: function(err) {
                        errorMessage.text(err.responseJSON.error);
                        error.show();
                    }
                })
                
            });
        });

    </script>
</head>
<body>
    <nav class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <a class="navbar-brand" href="/">Contact Information</a>
            </div>
        </div>
    </nav>

    <div class="container">
        <form id="phoneChangerForm" action="/update" method="post" style="width: 400px">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">Contact Information</h3>
                </div>
                <div class="panel-body">
                    <div class="form-group">
                        <label for="firstName">First Name</label>
                        <input type="text" class="form-control" id="firstName" placeholder="For verification" required>
                    </div>
                    <div class="form-group">
                        <label for="lastName">Last Name</label>
                        <input type="text" class="form-control" id="lastName" placeholder="For verification" required>
                    </div>
                    <div class="form-group">
                        <label for="email">Email</label>
                        <input type="email" class="form-control" id="email" placeholder="For verification" required>
                    </div>
                    <div class="form-group">
                        <label for="phone">Phone</label>
                        <input type="tel" class="form-control" id="phone" placeholder="New Phone Number" required>
                    </div>
                </div>
                <div class="panel-footer">
                    <div id="message" class="alert alert-info" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>
                        <span id="messageMessage"></span>
                    </div>
                    <div id="error" class="alert alert-danger" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-exclamation-sign" aria-hidden="true"></span>
                        <span class="sr-only">Error:</span>
                        <span id="errorMessage"></span>
                    </div>
                    <button type="submit" class="btn btn-primary">Update Contact</button>
                </div>
            </div>
        </form>
    </div>

    <nav class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <a class="navbar-brand" href="/">Contract Information</a>
            </div>
        </div>
    </nav>

    <div class="container">
        <form id="getContractForm" action="/getContracts" method="get" style="width: 400px">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">Contract List</h3>
                </div>
                <div class="panel-body">
                </div>
                <div class="panel-footer">
                    <div id="message" class="alert alert-info" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>
                        <span id="messageMessage"></span>
                    </div>

                    <!-- display retrieve contracts result-->
                    <Table>
                        <tbody>
                            <!-- <jS>data.foreach(d) => <tr>d.name</tr></jS> -->
                        </tbody>
                    </Table>

                    <div id="error" class="alert alert-danger" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-exclamation-sign" aria-hidden="true"></span>
                        <span class="sr-only">Error:</span>
                        <span id="errorMessage"></span>
                    </div>
                    <button type="submit" class="btn btn-primary">Retrieve Contracts</button>
                </div>
            </div>
        </form>
    </div>

    <nav class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <a class="navbar-brand" href="/">CRM AXG</a>
            </div>
        </div>
    </nav>

    <div class="container">
        <form id="getContractForm" action="/" method="get" style="width: 400px">
            <div class="panel panel-default">
                <div class="panel-heading">
                    <h3 class="panel-title">Product List</h3>
                </div>
                <div class="panel-body">
                    <div class="form-group">
                        <label for="contractName">Product Name</label>
                        <input type="text" class="form-control" id="contractName" placeholder="For verification" required>
                    </div>
                </div>
                <div class="panel-footer">
                    <div id="message" class="alert alert-info" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>
                        <span id="messageMessage"></span>
                    </div>
                    <div id="error" class="alert alert-danger" role="alert" style="display: none;">
                        <span class="glyphicon glyphicon-exclamation-sign" aria-hidden="true"></span>
                        <span class="sr-only">Error:</span>
                        <span id="errorMessage"></span>
                    </div>
                    <button type="submit" class="btn btn-primary">Retrieve Products</button>
                </div>
            </div>
        </form>
    </div>
</body>
</html>
2
  • can you confirm you're connected to salesforce at this statement - pg.connect(process.env.DATABASE_URL, function (err, conn, done){ – Ysr Shk Sep 24 '20 at 9:30
  • yes, As the first request "app.post" works perfectly if I press "update contact" on the app. Everything is fine. The contact is updated on Salesforce accordingly. But I am not able to retrieve contract information – Hermann Ouré Sep 24 '20 at 9:46
1

I have managed to solve the error: I needed to update my SOQL query

conn.query('SELECT Name,Product__c FROM salesforce.Contract WHERE Product__c != null'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.