4 Replies Latest reply on Dec 4, 2012 4:39 AM by Shauniedarko

    Creating Value Lists from Global Fields or Variables


      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.













        • 1. Re: Creating Value Lists from Global Fields or Variables

          Interesting problem. Is NetSuite really its own data engine? From a reporting standpoint, it looks like it's straight SQL, which might mean that any SQL ODBC driver would work. Actual has an ODBC drive for Mac and I've used it for years. It's not expensive as I recall. Might be worth trying. Alternatively, can NetSuite create and populate a view table (it's called different things in different systems), but basically it's a view-only table that might be based on a supported ODBC link for Macs/FileMaker. My thinking is that if you can get an ODBC link, a direct would be much easier to maintain than trying to keep tables populated.


          Based on your example, if you're able to get the values from NetSuite, then rather droping them into a single global field, I'd parse them into a table (delete all records, loop through the returned NetSuite values and create each as a record), then with a VL based on that table, you should be set.





          • 2. Re: Creating Value Lists from Global Fields or Variables

            NetSuite is like the bane of my existance.  I've used the Actual driver to connect to a 4D database but from what I've read, it won't work with NetSuite's dbase.  I might keep trying that route, but I can't find anyone who's done it.


            In your second solution, are you suggesting one table with records created and destroyed as needed?  I guess if I only needed a couple of values, I'd go with the table solution. Create a table for Colors, one for Vendors, one for Materials, and then update them as needed, but my problem is that I'm not sure what I might need at this point.  So I was hoping to create a solution that was a little more flexible.  Something that would allow me to create value lists on the fly without having to create a new table for a new type of value list.  Does this make any sense?


            I'm sort of trying to solve a problem that doesn't exist yet, so I may be going about it all wrong.


            Thanks for the help!

            • 3. Re: Creating Value Lists from Global Fields or Variables

              A conventional method of controlling what appears in a Value List, is to base its values from a field in a Table, and it's common to have that Table contain one value per record.


              A feature worth knowing about is that such a field can itself contain a list of values..  Meaning that rather than having many records, it's possible to have just one record.


              To populate a single field therefore means you can use a simple set-field command rather than importing or looping.  Such ease of making changes opens up many possibilities...


              I've attached a demo file showing 3 customisable Value Lists.




              • 4. Re: Creating Value Lists from Global Fields or Variables


                Thank you!  That is exactly what I was trying to do...and it wasn't working...which means that I've got a problem with how I'm parsing the data between the time I pull it from Netsuite and the time I put it into a field.


                Thank you very much.