bac mac

A database has about 2000 records, many of them with pdf copies of different documents, associated with each record, that are stored externally via containers. The database is fully encrypted and on a Filemaker Server on a Windows Server. It is getting so large that it takes too long to download backups and I'm also worried about the stability of the files. Each document is in a Container that is External (Secure). These documents don't actually require encryption, but we'd like to keep it if possible. The file and the container folders total about 5 GB. I want to keep the current feature where the record indicates which documents are available for that record and a simple click opens the desired pdf in Preview or Acrobat. I'd appreciate any recommendations. Options I'm considering: 1. Somehow reduce the size of each pdf. 2. Keep all of the files in a folder that does not involve containers and have the button open the file through a record and document specific link. I'm not sure this would work because I don't think that most of the users would have access to files on this secure Windows server if the documents weren't specifically in a filemaker container. They would not have any access at all to files on this Windows server directly.

The Set Field by Name solved it and works, along with Trim for most, and I'll tweak some substitutes for the others. I was able to loop through the 1238 imported records in a temporary import table placing the values needed in the fields in a single record in the desired table. Thank you very much!

The goal is to have, in a single record, all of the values from the .csv file. I am able to accomplish this is in excel, by copying all 1238 rows of the 2 columns and paste it into a new sheet with the transpose option. I was disappointed that I can't figure out how to accomplish this in Filemaker. I think the Set Field by Name that you suggested will actually work very well. I'll test that and report.

I've attached Test Metadata.fmp12 Table/Layout Raw Metadata=the direct import of the microscope capture metadata Table/Layout Final Metadata=the way I want the data to appear. (with the plan to have layouts display just the important parameters). For this example, in excel, I copied the raw metadata excel and pasted with the transpose option, but I'd rather do this in Filemaker and not require lab members to conduct the excel transposition. Thanks Test Metadata.fmp12

The latter. The .csv comes from the micrograph, not filemaker. The first table shown is just showing the top few records from the metadata file and I left out the commas. When I import the metadata .csv directly into filemaker, it ends up as numerous records with just the two fields Fieldname and Value. I want to, with a Filemaker script, convert all of those records into a single record in a different table where the value for the field Filenane would be BCB6 60xz.oir, the value for the field BitsPerPixel would be 12, and the value for channelLaserDataId#01 would be LD405. There would be about 100 different fields in the final record and that table is already created, but I'm having trouble figuring out how to easily populate it. Thank you

Metadata for our micrographs exports as a single .csv file that I can easily import into FM and contains two columns and about 100 rows. The column headings are something like Fieldname and Value. An abbreviated example would look something like: Fieldname Value Filename BCB6 60xz.oir BitsPerPixel 12 channelLaserDataId#01 LD405 etc. etc. I'd like to import the .csv, and rotate or transpose the table so that instead of 100 records, I end up with one record that has the following fields populated: Filename=BCB6 60xz.oir BitsPerPixel=12 channelLaserDataId#01=LD405 I already have a table generated from a transposition that I generated in Excel that has all 100 of the correct fields. I'd rather not have to execute the Excel transposition. It seems that I should be able to import the original .csv into a temporary table, export the information, then import it into the final table. Is the goal possible? Recommendations?

Actually, I found that this was a new bug that has cropped up in Filemaker and Claris is working on it: https://support.claris.com/s/article/SMTP-mail-fails-when-sending-an-attachment?language=en_US

Fitch- That was a good idea. After switching it to Documents path, it still failed to send the pdf, but I could see the saved file. Strangely, it did not seem to have an extension and had a google chrome icon. Double clicking it opened it in Chrome and it rendered the pdf OK. There is no Acrobat or Acrobat reader installed on that server- would that solve it? Thanks, Bruce

At wits end- When I try to send an email with a record saved as a pdf attached, it won't send. The email is sent fine via the SMTP server if I do not include the attachment. With the attachment in the send email script step, the error is 1506, can't send the email. The pdf is generated from a Save Records as PDF script step to $$ACORPReportTemporaryPath The variable was generated from Get (TemporaryPath) & "ACORPReport.pdf" If I email the value of the variable in the body of an email from the script, I get: $$ACORPReportTemporaryPath = /C:/Windows/Temp/S217/ACORPReport.pdf In the send email, for the file to attach, I have: $$ACORPReportTemporaryPath FMP 18 Server on a Windows machine with the script running as a scheduled script Suggestions?

Cool, I'll try the popover. I had looked at a couple of the starters for different approaches but I hadn't looked at Invoices- it seems to do a very nice job with this. Thanks very much.

It seems that WebDirect can't access a field under another field. This will be a problem for several solutions that I've set up over the years and I wonder if there is a better method for the following: For example, in an Orders table and layout, there is a field Requestor ID that contains a foreign key that would match the Primary key in the Personnel Table. For each Order record, there is only one Requestor. The Personnel table of course contains name, contact info, etc. for potential requestors. In the orders layout, I have an underlying field (Orders::Requestor ID), that is set for browse but not find, and set to a dropdown value list of the Personnel Table primary keys but displaying the second field Personnel::Name. Exactly on top of that field, set to find but not browse, I've placed the field Personnel::Name with a white fill to hide the underlying key. This works fine from the client. Clicking on the field produces the dropdown of names, and after the appropriate name is chosen, the correct name is displayed as the requestor. This does not work with web direct, so I'm wondering if there is a better way? It is important to maintain the relationship between the Orders record and the Personnel record through the foreign and primary keys. I'm wondering if I'm missing a new feature that would make this much easier to set up.

Thanks for the tips. Calling AppleScript worked fine.

With several different PDFs in different fields, and also in different related records, I need to combine the pdfs into a single pdf. I can save the current record Save Record as a PDF and this is the only script command that can append to an existing pdf. But that doesn't solve the problem of needing to append in PDFs from a variety of different container fields. For some reason, the Export Field Contents script step does not have an append option, as does Save Record as a PDF. Recommendations?

Usually, we only need to look at the exact matches because the most common test subject, Table A record, will match records in Table B that have sufficient cases for statistical significance. Failing that, we need to look further, e.g., include matches where there is one additional or one less disability- both cases would have a score of 8/9 compared to 9/9 for a perfect match. And yes, it will be entirely dependent on the test subject. We will include a limit on the mismatches and it may be 8/9 or possibly lower.

That is a very concise calculation that would do well identifying records with one mismatch and allow for that mismatch to be in any of the 9 fields. Would it also go beyond that and help evaluate less equivalent matches? The context for this involves a table (B) of hundreds to thousands (depending on which data set we use) each representing a subject type with 0 to 9 different disabilities, e.g., inability to walk without assistance, inability to bathe without assistance, etc. For each of these subject types, we have a record that indicates the status with respect to each problem. Each problem is given a field and a fully functional subject type would have able, or 1 in each of these 9 fields. A problem with one or more of these activities would have unable or 0 as the value for the field. (Actually, right now, it uses text- able/unable, but I'm considering switching it to 1/0- thoughts on that with respect to performance?) Then, in a separate table (A), we generate a new record with a test subject and populate the 9 activity fields in this table with the status for each of these activities. We can identify table B records that exactly match the state of the 9 activity fields in the table A record (easy via a single relationship matching each of the 9 fields between the tables). The question involves how best to identify the records in table B that include a mismatch and sorting them in order from best to worst match. What do you think?

From a record in Table A that includes 9 fields, I need to identify the records in Table B (of hundreds of records and include 9 corresponding fields) I can easily define a relationship that retrieves the records in Table B where the contents of the 9 fields exactly match, 9 out of 9, between the Table A query record and Table B records. How do I include the best, but not perfect matches, e.g., 8 out of 9 of the fields exactly match. I can script through all of the table B records and set 9 additional temporary fields with a 1 for exact matches and 0 for mismatches, but that seems cumbersome. I'm using Pro 12, but if 14 had some features that would make this easier, that would be fine. And just to be clear, I'm not needing to handle any mismatches between the corresponding fields. The corresponding fields either match exactly, or they don't. In fact, the fields could be boolean to start with and just contain a 1 or 0 each. What is the recommended way to determine the best match records involving a set of multiple fields?

I am accessing SQL tables from FMP 12 via ODBC and this has mostly been working fine, except for some performance issues that crop up now and then, e.g., Not Responding appearing at the top of the window for a few minutes now and then, but then it always clears and continues to function. I don't fully understand exactly how the connection operates or the best practices. For some of the connections, I just obtain a total count of the records and the names of all of the fields in the remote table. The remote table may contain 2-40 fields and about 80 records to 150 million records. Since the last time I added one of the remote tables to FMP, I keep getting an error in a FileMaker Pro popup window: "There is not enough room on the disk to complete this operation. Make more room on the disk, then try again." And the only option is to click OK. When I do, it goes away, but then reappears about 3 minutes later. This last table has 50 million records and about 30 fields. Unlike the other tables, I could not identify a unique field or a unique pair of fields. After trial and error, I ended up selecting all 30 fields to get it the table to appear. 1. Does the fact that I selected 30 fields when adding the table through the ODBC result in a performance hit affecting memory and/or disk space? (And, I have not had luck yet having a simple unique key added on the remote.) 2. What happens when I access SQL data through ODBC? Is the entire remote table brought into FMP memory? Regardless of what subset of the data I may actually be using? Would it be significantly more efficient if I did have a single unique key field in the remote table to use? 3. Any other advice about optimizing use of SQL data by FMP 12? Would there be significant benefits if we switched to FMP 13? Thank you, Bruce

That totally does it. And I greatly appreciate your speedy replies. I never would have come up with that syntax. I don't think I've ever used the Quote function. In my current incarnation, I have Table Name fed by a drop down list populated by a field calculation: TableNames("") and now, when I select the desired table, I instantly see the total record count in the SQL table desired. I'll investigate the other points that you mentioned. Thank you!!! Bruce

That solved it instantly! I had initially used the * and I also had tried escaping the table name, as well as too many things to mention, but never hit the correct combination. Now, I just tried playing with getting the TO name entered into the calculation from another field, and I tried escaping that as well, but can't get that to work. I also tried using the ?: ExecuteSQL ("Select Count (*) From ?";"";"";Table Name) Table Name is a field in the current table. I suspect I have to add a calculation to generate the appropriately escaped TO name. Do you have a suggestion for that syntax, with the TO name is in a field called Table Name? Thank you very much, -Bruce

I need some info from many tables from an SQL system and I have ODBC working and I can use the data through relationships with filemaker commands and almost everything is fine. I'd like to count the records in various SQL tables without setting up relationships and I've tried a large number of variations and searched the posts and web with no luck. For example In a calculation: ExecuteSQL ("Select Count () from Dflt.epCLC_Step3_RAN50";"";"") yields a ? This is a simple table of one field of scrambled IDs, just 50 records. Eventually, I plan to have the table specified by a filemaker field to present record counts for different tables that might be of interest. What do I need to do to get a record count without establishing a relationship?

The filter was hard coded into the portal, but I was trying to get away from using that portal. Good observations. Thanks you

OK, that is what I thought- having to add the additional field to Table A to accomplish the relationship, where with the portal, I could specify that in the portal filter. The instability I mentioned was due to the fact that it is a served file and I keep forgetting that globals can easily disappear. I guess globals in this case are best set during an open script. The value in the global Course Type in table A is going to be pretty constant and only rarely changed by the end user. Thanks very much for your help.

I'd be very happy with a filtered relationship, but I'm not sure how to best accomplish that. It was easy for the portal, but I don't see a similar mechanism without the portal. Is there?

In layout A, I can readily display the desired field value (date) from a related table (using a filtered portal where I filter to display related records where a different field (Course Type) in table B = a particular string. The relationships involves multiple fields and is also sorted to display the desired date. That works fine. I need to use that date (obtained from table B ) in layout A/table A in other calculations, so I need to place that date in a field in Table A. I'd rather not use a script for this. -> Is it possible to write a calculation that would result in the same value (date in this case) that I see in the filtered portal? (I can do this if I set up fields in table A that contain Course Type designators (globally stored) for each of the desired strings and then set up TOs of table B with the designator and the other parameters in the relationship. But this seems a bit unstable on this served file. It also seems a bit messy requiring the additional designator fields in table A.)

Right, I'll create the calculation fields and then determine the numbers in each state and avoid the excess finds that I was heading towards. Thank you.