Brian C

It has been my experience that filemaker FQL does not like it when you mix aggregate functions with non aggregate functions. If you have Filemaker Advanced you could write a recursive custom function that processes your SQL array result to arrive at the desired result.

I agree with Wim. Never use the existing database files after a crash. Always replace them from your last good backup. This may mean having to recreate data already input following the last good backup but it will save you a lot of trouble compared to what you will have to go through later on if your files become corrupted. You might want to think about adding more backups to insure you don't too loose much data entry. The 'Automatically Start Database Sever' option should never be used. If you are making use of 'Enable Progressive Backups' feature, it becomes even more critical to not use the autostart option. If you do, you risk overwriting your progressive backup with bad/corrupted data thus negating it's entire time saving purpose.

You could also use SQL to accomplish this. Set Variable[$result ; ExecuteSQL(" SELECT Type,count(Type) FROM MyTable GROUP BY Type ";"";"";"") ] This would give you exactly what you are looking for as well, but if you need to do anything specific with the result you will need to parse it. Be careful if you suspect your query will need to process/return record sets > 500. FileMaker will do it, but performance degrades quickly if the resulting data set that has to be processed is too large. Use of the WHERE clause is very important when dealing with SQL in FileMaker to keep your performance up to snuff.

The preferences you are looking at refer to what FileMaker will use as the default font when you CREATE a file. Unfortunately you cannot change the default font of a file after it has been created. You used to be able to change the default font for a layout by changing selecting a font while you do not have any objects selected, but it seems that we no longer have the ability to do this. So it seems that your only solution is to use Themes to control the fonts in v12 and 13. v12 has the Remove All Styles button which exposes the default font. v13 is missing the Remove All Styles button, so I believe you must really completely on Themes.

Another approach is to export the data to a fm formatted file and do your updates to the data and then import the data back in again without using the auto enter option on import. You will delete the old data prior to rein porting it. Just be careful of any possible delete related record settings in the relationships graph.

Yes, Custom Menus, Custom Functions, and Debugging are critical IMO.

Your solutions are always so simple. Thanks Comment! At least my 2nd shot at it did not mark records. LOL

After looking again, I remember looking through the documentation before and being a confused on the Local account options once I started to really dig in to the instructions beyond the summaries. After a quick search however, I found this helpful info from Soliant which is a bit easier to follow. http://www.soliantconsulting.com/blog/2009/02/using-local-os-accounts-for-filemaker-external-authentication When using cloud based FM servers, is this is even an option?

A cursory check of Scribe documentation seems to indicate that it uses the newer .xlsx format only and not the older .xls format. The error seems to indicate it wants a .xlsx formatted file. Maybe it is the name of the file suffix, or you are modifying a .xls file with .xlsx file attributes which is causing the error?

UPDATED! This was a trick that we used to be able to use in FM 11. Unfortunately this technique no longer works now since objects do not render outside of the container they are a part of. I suspect this was in preparation for WebDirect so that things would have mirrored behaviors between Client and WebBrowser. You can accomplish this using a looping script to capture the ID of the first record for each break and store it in a global variable. In FM12 you can then use conditional formatting to change the text color to 'hide' it. In 13 you can actually hide the object completely. The calc used to hide the data would be: IsEmpty ( Filtervalues ( $$ListToSearch ; MyTable::RecordID )) Now as for how you would accomplish this in your script: Set Variable[$$ListToSearch;""] SortRecords[Restore;NoDialog] (ClientID;Project) Go To Record/Request/Page[First] Loop If [ isempty($ClientID) or $ClientID ≠ MyTable::ClientID ] Set Variable [ $ClientID ; Value:MyTable::ClientID ] Set Variable [ $$ListToSearch; Value:List($$ListToSearch;MyTable::RecordID) ] End If Go to record/Request/Page [Next;Exit after last] End Loop

FileMaker uses the "!" as a means to find duplicate entries in a single field, but what you do with this simple functionality is another matter entirely. Location of duplicates can be a bit complex depending on how exact or fuzzy you want your results to be. Duplicate searching in filemaker can only give you exact matches, so misspellings or sounds like become issues. You should look into using a SoundEx custom function (check brian dunning's website) to assist with this process to get a 'sounds like' type of phonetic match if you really want it to be more powerful. As for how you collect the results of your searching, you may want to dump the IDs into a temporary table tagged with the user account so you are only storing 2 pieces of data. There are fancy techniques to store results in a global variable so that the data is not stored physically but lets keep things simple for the purposes of this post. Doing it this way, you can present the user with a layout of search results. Just make sure you do a cleanup of the records when they make a selection or choose an action. And just in case they loose connection to the server for some reason, make sure there is a search that occurs up at the very beginning of your script so that it can do a cleanup in case there are records still out there.

This is why I love this board. Nothing but good happens when knowledgeable people come together to share information. I will look into this option more. Thanks Wim!

Instead of hiding the toolbar, use custom menus to take control of the Delete Record menu command which in turn will also take control of the delete toolbar button. The menu can be pointed to a script that you write to take complete control of the process. Modifying the privilege set should also be done as Wim describes so that more clever people cannot find a way around your UI to do things they should not.

You could use a layout script trigger instead: onRecordCommit. This could fire a script to see if the record contains something which marks it as locked. If so you would issue a revert record script step to prevent the save.

Is your field on the layout that you are attempting to do the insert for?

Separation model is just a fancy way of saying that your interface and scripts are stored in one file and your data is stored in a separate file. You then link your UI file to the data file and in that way, if you make changes to the UI (aka business logic) file, you can just swap out the old UI for the new UI and be on your merry way without affecting the data. If you make changes to both, you just need to keep track of schema changes to the data file carefully so that you can replicate them in the exact order you did them, and then swap the UI file after you are done. This way nothing breaks.

Yes that is great as long as External Authentication is an available option. Not everyone has an Open Directory or Active Directory server handy for this type of thing.

I think that the nesting that teaaddict is referring to is embedding each of these reports into one another (excluding the first page of companies) so that each company's department and staff pages are all grouped together. This way someone does not have to sort it all by hand to make sure the right bits are all where they should be. Hence the reason for referring to loops and counts. That being said, Teaaddict: as long as you are dealing with a found set of records you should be able to use the function get(foundcount) to determine how many records are in the current found set instead of having to do a loop just to get a count.

The Max function is designed to return a summarized (aggregate) result for multiple records. Unfortunately FileMaker only allows you to return one column of data in addition to the summary function you choose wether it be SUM, MAX, etc. So this works: SELECT MAX(CityStateZip_ID),zip FROM CityStateZip WHERE city='Wilmington' But this does not: SELECT MAX(CityStateZip_ID),City,State,Zip FROM CityStateZip WHERE city='Wilmington' Similarly GROUP BY is also used with summarized data (aggregates). It is important to note that according to the documentation, you cannot reference a column in GROUP BY without first referencing it in the SELECT. So this works: SELECT Zip,MAX(CityStateZip_ID) FROM CityStateZip WHERE city='Wilmington' GROUP BY Zip But this does not because the column Zip is not referenced in the SELECT first: SELECT City,MAX(CityStateZip_ID) FROM CityStateZip WHERE city='Wilmington' GROUP BY Zip ORDER BY won't work in the same query as GROUP BY (At least in FileMaker) because GROUP BY is designed to work on summarized data (aggregates). As a work around I recommend you store your MAX functionality as an unstored calculation field in your CityStateZip table so that the result can be pulled in the query as a standard column. If you create a relationship based on the City Name to a duplicate TO of the CityStateZip TO, you can just use max(CityStateZip_MAX::CityStateZip_ID) in the unstored calculation field. What you want to use the GROUP BY for can be done within the ORDER BY to some extent. Your query Was: SELECT MAX(citystatezip_id),city,zip FROM citystatezip WHERE city='Wilmington' GROUP BY zip ORDER BY citystatezip_id Which if rewritten to a close approximation, would be: SELECT MaxCityState_ID_c,City,State,Zip, FROM CityStateZip WHERE city='Wilmington' ORDER BY Zip,CityStateZip_id And the results returned look like this: 42048,WILMINGTON,MA,01887 42048,WILMINGTON,VT,05363 42048,WILMINGTON,NY,12997 42048,WILMINGTON,DE,19801 42048,WILMINGTON,DE,19802 42048,WILMINGTON,DE,19803 42048,WILMINGTON,DE,19804 42048,WILMINGTON,DE,19805 This is probably not exactly what you are looking for but I believe that you can probably make use of INNER JOIN to get to the result you want. I tried using DISTINCT but it does not work well with multiple columns that fall outside of the DISTINCT function. To summarize, MySQL seems to be a lot more flexible with SQL functions beyond their original intent. This flexibility is great, but FMSQL is not quite there yet. On the plus side FileMaker does at least give us the tools to still be able solve the problem in other ways.

Not sure I understand the question completely but here goes. I downloaded your sample file and it contains only a single record and no relationships on the relationship graph. Because you only have a single table in your file, I must assume you want to know how to create a relationship in order to see data from the same table that you are already in. The question becomes what data do you want to see in the portal? What should the relationship be based on? What piece of data will the related records all have in common so that you can, in the simplest sense, have one value = another value? Since it seems you need a practical example of a relationship for the purposes of creating a portal, I have modified your sample file to those ends. In my example I want to see all records that share the same ID number. I duplicated the first record multiple times so that we now have multiple records with the same ID. Next we go to Define Fields... Relationships. I duplicated the Untitled table occurrence and renamed the duplicate Untitled_Portal so that it's purpose is clear. I then use the ID field to create a link between the 'Untitled' and 'Untitled_Portal' table occurrences. It is important to note that your current layout views the current record from the context of the 'Untitled' table occurrence. The relationship between Untitled and Untitled_Portal provide the means for you to view any other records that have the same ID in common using the Untitled_Portal table occurrence which is based on the same table. I think from looking at your expiration date calc that is broken, you wanted to have the expiration date based on the original date combined with the period? I added a new calculation field ExpirationDate_c. If this was not your intent you can just delete it. This new field adds the number from the period field to the year from the date field to create an expiration date. The calculation is: Date( Month(Date) ; Day(Date) ; Year(Date) + GetAsNumber(Period) ) I then changed the period field on the layout so that it uses radio buttons instead of check boxes so that only a single option can be selected so that the calculation can correctly add either 2 or 5 to the year. Last I have created a portal on your layout by choosing the portal option in the toolbar in layout mode. I pointed this portal to the Untitled_Portal table occurrence and chose the fields I wanted to see. Hope this helps you to understand how portals work! Untitled.fmp12.zip

FileMaker does not do this very well, at least in my past experience. Printer switching is generally an issue that is handled by a plugin. There are a few different companies that make plugins that handle this: http://myfmbutler.com/index.lasso?p=367 http://www.productivecomputing.com/change-printer http://dracoventions.com/products/2empowerFM/family/print.php http://www.practicemaker.com/setprinter.html http://www.softtechsolution.com/change-my-printer/ http://www.monkeybreadsoftware.de/filemaker/

Yes I do miss Hot-swapable drives, however all is not lost if you can afford a separate rack mounted raid. Apple is due to release a new Mini any day now so anyone thinking about using them might want to wait. That being said the current 2.3GHz/2.6GHz quad-core Intel Core i7 Mac Mini configured with 16GB of SDRAM and 2x256GB SSDs is certainly no slouch from a performance stand point. The Sonnet gives you the missing Ethernet Port and some additional expansion. Plus you get Thunderbolt , USB 3.0 and HDMI out. FileMaker's biggest bottleneck has always been I/O for the Drives which is why SSDs are so much better from a performance standpoint. But this is also true for any database platform really. WebDirect on the other hand benefits directly from a better processor and lots of RAM (or so I believe). I know a lot of mac based server farms are using Minis these days because they are so cheap. They just swap out the entire unit if something goes wrong. Others are using the new Mac Pro if the processing power is really crucial.

Perhaps this may have more to do with the config of your server OS than FMS 11 or the file itself? Alternately make sure the regional settings on the file are correct as well. You will have to open the file locally to change them. See the following link regarding the File Options menu command and the Data Entry portion where it describes these settings. http://www.filemaker.com/help/11/fmp/html/create_db.8.4.html

We have replaced our Xserves with Mini Serves with dual SSDs and we use an expansion chassis from Sonnet which works rather nicely for adding what the Mini lacks. http://www.sonnettech.com/product/xmacminiserver.html About the only thing that is missing now is a redundant power supply.