Published on April 11, 2021.

One of the NetSuite integration projects that I've been working on is a custom mobile app for a client's sales team. The sales reps have been issued iPhones and iPads, and the goal of the app is to provide easy and reliable remote access to customers, prospects, inventory, and more. In addition, the sales reps need to be able to work both online and offline.

Using FileMaker Go

What made this project particularly interesting was the platform that the mobile app was developed on, because in this case the client chose to use FileMaker Go.

FileMaker Go is Claris's free FileMaker client for the iOS platform. You can use it to connect to hosted FileMaker databases as well as local databases. In this case, I developed a FileMaker database that the reps access locally, and the database automatically syncs with NetSuite via a custom RESTlet.

Here's a screen shot of the app's Opportunities list layout.

Using SuiteQL to Get Opportunities

One of the things that app gives sales reps access to is their opportunities. Here's a query that I'm using to retrieve the open opportunities for a specific sales rep.

	
SELECT
	Transaction.ID AS TransactionID,
	Transaction.TranID,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ' ) AS Status,
	Transaction.TranDate,
	Transaction.ForeignTotal,
	Transaction.Entity AS CustomerID,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName
FROM
	Transaction
WHERE
	( Transaction.Type = 'Opprtnty' )
	AND ( Transaction.Employee = 7415 )
	AND ( BUILTIN.DF( Transaction.Status ) IN ( 'Opportunity : In Progress', 'Opportunity : Issued Quote' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID

Notice that I'm filtering on the transaction type ("Opprtnty"), the sales rep (represented as the "Employee"), and the transaction status.

A Note About Transaction Statuses

If you've ever tried to query the Transaction table based on the Status column, then you might have run into some odd SuiteQL behavior. Under the hood, it appears that the statuses are actually stored as characters.

For example, if you run this query...

	
SELECT DISTINCT
	Transaction.Status,
	BUILTIN.DF( Transaction.Status ) AS StatusName
FROM
	Transaction
WHERE
	( Transaction.Type = 'Opprtnty' )
ORDER BY
	Status

... then you'll get a result that looks something like this:

	
A	Opportunity : In Progress
B	Opportunity : Issued Quote
C	Opportunity : Closed - Won
D	Opportunity : Closed - Lost

You would think that you could query for opportunities that are in the "In Progress" and "Issued Quote" statuses by doing this:

	
SELECT
	Transaction.ID AS TransactionID,
	Transaction.TranID,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ' ) AS Status,
	Transaction.TranDate,
	Transaction.ForeignTotal,
	Transaction.Entity AS CustomerID,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName
FROM
	Transaction
WHERE
	( Transaction.Type = 'Opprtnty' )
	AND ( Transaction.Employee = 7415 )
	AND ( BUILTIN.DF( Transaction.Status ) IN ( 'A', 'B' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID

However, that query returns no rows. As a result, I ended up having to filter on BUILTIN.DF( Transaction.Status ). This is, admittedly, terribly inefficient. However, it works.

Wrapping Up

This has been a really interesting project to work on. While I've worked on several projects that involved integrating NetSuite with FileMaker-based solutions, this is the first opportunity that I've had to integrate NetSuite with FileMaker Go. The mobile aspect of the project, including the sync function (which was based on my old EasySync solution), also made the project a little challenging.