I'm not sure that the solution I'm hoping for exists, so I'm kind of looking for some help thinking outside the box.
The situation is this: I'm working on a solution for a company that uses NetSuite as their ERP. To avoid data duplication, we want to integrate with NetSuite as best as is possible. The office is completely Mac based, so accessing NetSuite as an external data source via ODBC is a no go, since there isn't a NetSuite ODBC driver for Mac that I could find. I looked into using WebServices and was set to try to do calls using a WS plugin when a coder in the office and I got together and he built a plugin that handles all those calls. So I'm able to get data from NetSuite easily.
The problem is what to do with that data. An example is this: they keep a list of product colors in NetSuite. The solution I'm building is a project management solution. So when they start envisioning a new product, and go to enter the possible colors for said new project, I want them to be able to draw on the existing colors to avoid errors later on (this is a simplistic example but works for Vendor names and such). The problem is that I would like to be able to call the color list from NetSuite and have it show up in a value list.
The most straightforward way of doing that, that I can see, would be to create a table called Color, run the script, import the color names, and have the Value List look up from that. Then, nightly, I could run scripts that updated the value list, or allowed a user to update the value list as needed. But then I'd need to create tables for every Value list I wanted to have. Colors, Vendors, Parts...essentially recreating NetSuite in FileMaker.
My thought was to use Global Fields for the value lists. At the beginning of the FM session, the opening script would gather the list of colors and populate that global field. Then, I could create a cartesian join with whatever table I needed the value list in and base the VL off the global field. And that works...except it treats the values as one value. The dropdown shows the list of colors, but they are treated as one value and individual values can't be selected.
So the process is like this:
The script calls for the requested values from NetSuite. It returns the following values(
I take those values and put them in a global field, and the dropdown shows one value of
It treats them as one value even though I can manipulate them as separate values.
I'm not even sure if what I'm attempting to do is possible or if there is a better way. I'm definitely open to suggestions.