sal88

Hi all I have moved a "message object" field from my emails table to a separate FM database called "MessageObjects". The server schedule runs a receive emails script, which now references that new database as an external data source. Is there a simple way for the script to check whether that external data source is accessible? I would like the script to halt if it is offline. I've tried get (layouttablename) from the layout that points to the external table occurrence but this refers to the table occurrence whether the source is available or not. The best I've come up with so far is to get it to perform a script that resides within MessageObjects. Thanks

I added a 'minimum log id' field to the customer record table, to be populated on record load when the number of related records goes over 500. It was the 500th newest log id for that customer. So the relationship would show records where the log id was above that in the new 'minimum 'log id' field. While it was an improvement, it was still slow, from what I can tell this was due to the "greater than" operator in the relationship. I'm not sure if this would have been different if it were a date field. Instead, I've added a new 'top 500 client id' field to the Jobs/Cases tables. This is an auto enter field which is populated with the client id. So the relationship is clients::client_idf==cases::top_500_client_idf. To maintain this I have a daily scheduled server script which starts with an ExecuteSQL query: select top_500_client_idf , count(*) as case_count from cases where top_500_client_idf is not null and closed = '1' group by top_500_client_idf having count(*) > 500 From there it then blanks the top_500_client_idf field where the record is not part of the latest 500. I'm trying to think of a simpler way of doing this, without looping through records, e.g. a lookup. This solution means the user does not have to perform any queries or match key population on record load. Thanks for all your help guys!

So the relationship should be based on a multikey field?

Great article and I've implemented all the fixes however I can't find a recommended way to limit the number of records that the relationship pulls in. Is it enough to just have a script populated global date field (e.g. thirty days ago) and then base the relationship on that? Or is there a better way? Ultimately I just need the most recently created hundred or so records. Thanks

That's right, sorry it completely slipped my mind. Hopefully that's a sign that I can remove the capability! Thank you for the article I will read it right now. Ah I understand. That would explain why FileMaker freezes when I try to search those 10,000 related records. Does that mean that even if the free text filter field is empty, it still performs 10k evaluations, because the filter includes the line: IsEmpty ( g_filter )

Thank you Comment! I guess that means the fewer related table occurrences the better, even if they are effectively not on display via a layout object. Or is the sorting of them that affects performance? Does it make any difference whether the sorting is done via the relationship, or the portal? I'll test the changeable global field approach, however I've just realised it might not be an option as I have a multi window configuration, so one window might have the Jobs tab open, and the other the Items tab. However multiple windows is I'm sure is another problem for speed so I'll find out if anyone is actually using multi window. If I have to stick with multiple window, I will have the multiple TOs but ensure that the filtering is done via the relationship, and not the portal (with the exception of the free text filter), as you suggest. Yes I think it is time to put a limit on the number of records that it is pulling through. I take it that should be done via the relationship? What is the best field to filter by? I may have misunderstood where problem lies with so many related records, is it the display of them in the portal, or searching the portal, or a bit of both? Do you have an idea of what the maximum number of records should be? I'd say the average customer has 800 related type 2 records. And we have thirty users over WAN, who will all have this layout open (now I think about it I'm not surprised there are performance issues!!). I'll post the other scenario separately. Many thanks

Hi all I can't work out what combination of relationship/portals/portal filters is best for performance. The first scenario is the customer record which has three portals that all ultimately point to the same Log table, the portals are mainly distinguished by the value of the Type field. They are all in their own tab panel so only one is displayed at a time. The portals also have a "free text" filter which refers to a global field, so users can search the portal. The portals display the following data: Recurring Services (type = 1 and isempty (invoice_idf)) - 100 records - sorted descending by Log_ID (number field) Jobs (type = 2) - 10,000 records - sorted descending by Log_ID Items (type = 3) - 920 records - sorted descending by a "due date" field - but could probably be changed if it were an issue The record counts are as per the 'busiest' customer - which is quite slow to load. So I'm wondering what configuration would be best when it comes to: Sorting - should this be via the relationship, or portal, or both? Filtering - relationship or portal or both? Table occurrences - one shared by all portals, or one for each portal? The second scenario is similar but relates to the Invoice record pulling in that same Log table data for totals. The Invoice record has four calculations which I believe are the source of slowness: Recurring Services total (currently looks to all Log records that have the required Invoice ID, and a type field of '1') Jobs total (same as above but type 2) Items total (same as above but type 3) net total - sum of the above three In this scenario, should there be a TO for each of the top three calculations, or a single TO, and then the addition of three line_net_sale fields in the Log table - each one populated depending on the Log's type field, so the Recurring Services total calculation would be sum (invoices::line_net_sale_RS) for example. Also, should the net total add up the other three calculation fields, or should it point to its own TO, which shows all Logs of the same Invoice ID? All advice appreciated!

Seems to be resolved with 19.3 thanks to the Edge/Chromium browser

Hi all I have moved one of my databases (MessageObjects) to a subfolder (ContainerData) on FMS. I only moved the fmp12 file. FMS seems to have automatically created an RC_Data_FMS folder within ContainerData, and within it, there is a folder called MessageObjects. However in the root folder, where MessageObjects originally was, there is still, within the RC_Data_FMS folder, a MessageObjects folder. This isn't an issue as none of MessageObject's container data is stored externally. However I want to move another database to a different subfolder, and this one does have a lot of externally stored container fields. What is the best way to go about this? Just move the FMP12 file, or move both it and the corresponding RC_Data_FMS subfolder? Thanks I managed to work it out by looking at the container data options within the database. I saw that it's all relative. So I just moved the database and corresponding RC_Data_FMS folder to the new subfolder and it was fine.

Hi all We are considering a javascript based task list to be shown in the web viewer. My query is how this can work in terms of ensuring everyone sees the most up to date data. e.g. user A adds a task in to the web viewer page, that then executes a FIleMaker script which adds that data in to the appropriate table. User B will not see that new task until they refresh the browser. Refreshing is not ideal because it might mean they lose what they were working on. Instead a Javascript function would need to be executed, either from within the web viewer or from FileMaker. This would pull in any new data in to the appropriate html element without refreshing the whole web viewer. Is there a way that when user A makes a change, User B's client is told to perform the 'limited refresh' described above? As far as I can see the alternative is to use timer scripts - either complete javascript/webviewer based or from FileMaker. Thanks

Hi all We have a PostgreSQL 9.6.17 database that we wish to connect to from FileMaker. I have added the PostgreSQL Unicode(x64) 9.06.01.00 in to ODBC (64bit) on FileMaker Server and succesfully added a DSN. From FileMaker Pro this DSN shows and I'm able to add it as an ESS. However when I go to the relationship graph and try to add one of its tables I get the following message: "The ODBC data source you have selected is not supported." According to this table it is actually PostgreSQL ODBC Driver (Unicode) 9.03.04.00 that is required. However this is not 64 bit (I tried it anyway but it does not show up at all in ESS in FileMaker Pro): https://support.claris.com/s/article/Which-ODBC-data-sources-drivers-are-supported-with-External-SQL-Data-Sources-1503692938173?language=en_US The table also says that we require the Actual Technology Adapter 1.5.0, but I can't work out if that's just for mac. Any help appreciated. Many thanks

Just to clarify I am referring to text within forms. Ctrl does work with ctrl+c and ctrl+v, but not ctrl+z, ctrl+y, ctrl+Home, ctrl+end, or ctrl+a.

Hello all The ctrl key does not appear to work within the web viewer in FM pro on windows. This would be useful for text selection e.g. select all. I've implemented a restricted menu set in the hope that keyboard shortcuts would not activate anything outside the web viewer but this has made no difference. Does anyone know a workaround for this? Thanks!

This has been resolved by using the web viewer with some forms and javascript.

That is very interesting. Thank you comment.