Diver

I too have experienced this problem 2 times in the last 3 weeks though on a Windows server platform. Will try optimization as well. I do have log files I will share with 360works.

This portal is going to show summary data in several other columns. Thus I need to identify each unique invoice and I will then use the uuid to get summary data. I believe my logic is correct in what I am trying to do, my syntax is just not correct. As noted above, I get an error when I try to do a MAX(), even with out the group by. This might explain it better, the reason I can't just grab the invoice number is the sql result is being used as a portal filter. If I grab the invoice number I will get all duplicate invoices. I just want one invoice per portal row. I am unable to use the relationship graph to filter in this unique case because of some of the data I am displaying in the portal would break.

After reading this thread I believe the GROUP BY would give me what I want but I believe my syntax is wrong and thus I am getting a '?' returned. My actual calc uses the 'Let' function for things but I tried to simplify things for this post. I have a list of invoice that repeat and I only want one of each AND the UUID for each of them. As example my data looks like this: 64320 C3990C50-92E9-1F4B-A3AF-3EE9329FBD04 64320 D2B65E90-A187-C043-B454-EDBEFDF32EB1 64320 D98BF80C-E1A9-0044-93C2-6F12F5713FE1 64565 0042E320-F937-E145-BF22-DD9792E4FA82 64565 05A930D5-B2BD-7340-8182-6F40F216F858 64565 0670C290-E938-4246-A5D6-2C6470A3FFB5 64565 2CAB9CEC-FD22-634C-ADAE-44220EB3C0CC I want the result of my sql to be like this: 64320 C3990C50-92E9-1F4B-A3AF-3EE9329FBD04 64565 0042E320-F937-E145-BF22-DD9792E4FA82 I really don't care which of column 2 is returned for any given invoice (column 1), I just need distinct column's 1 with the column 2. When I try using the DISTINCT function it considers every record unique because the column 2 is always different. This works for getting the whole list: Let( vResult=ExecuteSQL ( "SELECT \"Invoice\", \"UUID\" FROM \"Sales\" a WHERE a.\"ItemID\" = ? " ; " " ; "|*|" ; 12345 ) ; Substitute ( vResult ; "|*|" ; ¶ ) ) As soon as I try doing something like this, I get a '?': Let( vResult=ExecuteSQL ( "SELECT \"Invoice\", MAX(\"UUID\" ) FROM \"Sales\" a GROUP BY "\Invoice\" WHERE a.\"ItemID\" = ? " ; " " ; "|*|" ; 12345 ) ; Substitute ( vResult ; "|*|" ; ¶ ) ) Even if I take out the 'GROUP BY' line, I get an error, meaning there is something wrong with my 'SELECT' line. Until I fix that I can't tell if my 'GROUP BY' will give me the result I want. Any suggestions on what I have wrong?

Thanks Wim. I sometimes forget about 'rolling your own'. In this case it would have been ugly as there would have been over a 1,000 ?s. A recursive would have worked if I did them one by one. I stumbled across a Custom Function on briandunning.com that fit what I needed perfectly. You simply give the CF a list and it does the rest for you. VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )

Believe I found the solution. The list has to look like this: WHERE a.\"PartNo\" IN ('0110015004', '0110015005', '0110015008')" ; Setting the variable $itemIDs to that same list and then using: WHERE a.\"PartNo\" IN (" & $itemsID & ")" ; does NOT work Nor does setting a global field and using SELECT to pull the data from the global such as: WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"INV__Inventory\"))" ; Unless someone could provide a way to use a dynamic list of items to use with the 'IN' option I think the 'IN' option has a very narrow focus and use, though good were it will work.

Thanks Justin for the reply. I have changed the string of values to be what you outlined, not return-separated. I have my two original examples slightly modified trying what I thought your reply suggested yet neither work. I am now trying do a sql query in all records in the INV__Inventory table and only return those numbers that are in the "IN" string. Here are the two calcs, along with their results. Example 1: Though I am setting the $itemIDs, it is only being used to show what the field value of sqlSelect_g is. It was my understanding that this method, using the "IN( SELECT...." is needed as Kris M noted above. Let ( [ $itemIDs = INV__Inventory::sqlSelect_g ; SQLResult = ExecuteSQL ( "SELECT a.\"PartNo\" FROM \"INV__Inventory\" a WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"INV__Inventory\"))" ; " " ; "|*|" ) ] ; "SQLResult is: " & SQLResult & ¶ & "When $itemIDs is: " & $itemIDs ) ----------- RESULT OF CALC ------------ SQLResult is: ? When $itemIDs is: "0110015004, 0110015005, 0110015008" Example 2: Here I am using the "IN" clause without the SELECT clause, corrected as you pointed out in your previous post. Let ( [ $itemIDs = INV__Inventory::sqlSelect_g ; SQLResult = ExecuteSQL ( "SELECT a.\"PartNo\" FROM \"INV__Inventory\" a WHERE a.\"PartNo\" IN (" & $itemIDs & ")" ; " " ; "|*|" ) ] ; "SQLResult is: " & SQLResult & ¶ & "When $itemIDs is: " & $itemIDs ) ----------- RESULT OF CALC ------------ SQLResult is: ? When $itemIDs is: "0110015004, 0110015005, 0110015008"

I have run into a similar situation trying to use the 'IN' clause but have not been able to find a resolution. I have two FM files. The first one uses a sql call to capture a long list of part numbers (see example list below). I then pass the results to a second file in a script parameter. Example list: A12345 A85732 B45449 .... could be 1,000 items in the list Here is what I have tried: Let ( [ ReturnSub = "\n" ; // Swap out carriage returns in results with a different character. \n is the default. $itemIDs = Get(ScriptParmeter) ; SQLResult = ExecuteSQL ( "SELECT a.\"PartName\" FROM \"Inventory\" a WHERE a.\"PartNo\" IN ($itemIDs)" ; " " ; "|*|" ) ; partNames = Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ] ; partNames ) I have also tried using a global field (sqlSelect_g) in the second database and setting the passed parameter list into the field so it has a long list of items for the use by the SELECT clause : Let ( [ ReturnSub = "\n" ; // Swap out carriage returns in results with a different character. \n is the default. SQLResult = ExecuteSQL ( "SELECT a.\"PartName" FROM \"Inventory\" a WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"Inventory\"))" ; " " ; "|*|" ) ; partNames = Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ] ; partNames ) I think I would be fine if I was in one file and could use the SELECT clause within the IN clause but the data I need for the IN clause is not from the file I need it for. Any suggestions?

Great example, thanks sharing it! What would be required to get distance by vehicle?

I want to use the SCMoveLocalFile command with a FM Server Side Script that moves files to the trash can rather than use the SCDelete. This is because the SCDelete makes the file gone for good where if it was moved to the trash during testing, it could be removed from the trash and put back in the FileMaker Server Documents folder. I have tried using the destination path of "~/.Trash" but I get an error. Anyone have a suggestion? Related to this, can you move a file to the Admin accounts trash without the FileMaker Server machine actually being logged in with a user account? For testing I do have it logged in.

Good news, the problem is solved. While going through the manual process I learned that file /Library/FileMaker Server/Admin/admin-helper/WEB-INF/conf/mod_jk.conf was missing the following two lines: JkFmMount /SuperContainer/* cwpe JkFmMount /SuperContainer cwpe I am guessing that these were removed when using the FileMaker Server Admin Console and running the "Edit Server Deployment", selecting to use FileMaker's built in PHP versus the installed PHP. I am not certain on this but it is the only thing I can think of that may have caused the lines to disappear. Thanks for the help!

I have been using SuperContainer for some time with no troubles. Now my FileMaker databases are not seeing the SuperContainer URL. When I try and access the standard url of 127.0.0.1/SuperContainer using Safari on my web server I get the error: The request URL /SuperContainer was not found on this server. I have validated it is there, as are all of my files (whew). I have checked permissions for the Shared directory with SuperContainer directory in it and I think all looks fine, though I am not sure what they should be. It is running on an Apple Xserve with Server OS 10.5.8. I ran Disk Utility and repaired permissions. I downloaded the latest SC version 2.77 and installed it with the Installer.jar file. I have checked and IWP is working fine. My FileMaker 10 Advanced Server is running on a separate Xserve in the same rack. Anyone have any thoughts on possible problems or testing?

I can understand the need to do custom work for organizations but I would hope you could add some things like the image info to a future version. It is a very general feature that would benefit everyone.

I am uncertain how far I should try the testing, based on the last post. I have tried to implement it but believe where it is breaking is the following. (I have the first line for reference only). $file = Get (FileName) 1 + Left( EvaluateGroovy( "fmpro.performScript("" & $file & "","Show"); return true;" ); 0)) The second line is the line in the conditional formatting that I need to change. The reason is the filemaker file I am using is hosted on a server. It appears the code above is formatting the path to the local file so it can tell ScriptMaster to run the file's script called "Show". I tried replacing a portion of the text to be something like: EvaluateGroovy("fmpro.performScript( "fmnet:/serveripaddress/fmFileName","Show");return true;);0)) serveripaddress = the IP Address of my FM Adv. Server fmFileName=the hosted FileMaker database Show= the script within my FileMaker database to run I am guessing I have something messed up with the quotes and am not able to find documentation on what EvaluateGroovy is expecting for parameters.

This is a bit mind bending. I see in your example how the Conditional Formatting is using ScriptMaster plugin. I will have to try and dive into it deeper and see if I can apply it to my solution. To summarize, it is basically using a conditional formatting to tell ScriptMaster to run a script. - correct?

Thanks David. I had thought of that as well but it is such a nice feature to be able to just drag and drop. Much easier than having to go through a file tree to locate the file already at your fingertips. Could SuperContainer be enhanced to allow the FM record to be saved upon modification of the web viewer content? This would allow script triggers to then be available.

Thanks for the thought ... I had been thinking the same thing. The problem is FileMaker does not see the web viewer as changing so the un-stored calc using SCGetInfo does not change unless you force FileMaker to record the record by refreshing or clicking outside of any object.

I wanted to have a script trigger launched upon modification of the SuperContainer web viewer. If there was an image in it, it would do one thing. If there was no image in it, it would do another. I have found if I drag a file into the web viewer no script is ever triggered. If I control click on the web viewer and delete an image and then click on the layout, a trigger will activate. Is there any way to make the dragging of an image into the web viewer activate a script or for a script to be activated when deleting an image from the web viewer without clicking on the layout?

So many threads talk about the iwp_home.html file yet I can't find a copy of it. FileMaker docs say it is on the Install CD. I only deal with volume licenses and thus download the install from FileMaker. There appears to be no file called iwp_home.html from the install. I have looked on the server in the /Library/FileMaker Server/Web Publishing/IWP folder and it is empty. Does anyone know where I can find it or get a copy of it?

The Console is actually accessed through your web browser. Enter the following with the xxx.xxx.xxx.xxx replaced by the IP address of your web server where you installed the FM software: http://xxx.xxx.xxx.xxx/fmi/config