Do you have FileMaker 12 or newer? This can be done with ExecuteSQL calculation fields and they will then update automatically when data in the related table changes.
I have FileMaker Pro 13.0 v.1 Advanced
Have you ever created a SQL query before? This method allows you to do the whole evaluation in one calculation field, but if you have never used SQL before, it can be a very steep learning curve.
Other methods using some calculation fields in your Human_Testing table combined with a calculation field in your Human_PatientInfo field can produce the same results.
I have never created an SQL query in FileMaker before, and I have very limited knowledge of performing an SQL query in Microsoft Access. Which way is the best way to go?
It depends on whether you want to invest the time in teaching yourself SQL or not. The biggest current draw back to using ExecuteSQL in current versions is that if you make even the smallest syntax error in putting together your query, the function returns a ? and you have no feedback telling you where the error might be in your expression. SeedCode offers a very nice tool for building your own queries that helps minimize the syntax errors: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
And if you open up this document: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
You'll get FileMaker's info on what keywords, functions etc you can use in a SELECT query in ExecuteSQL.
Here's some examples of nonSQL solutiosn for some of your fields:
If IllnessPhase = "Acute" AND SampleType = "Serum", THEN Serum1Collected = "Yes" AND Serum1Collected Date = DateCollected
Put a one row portal to Human_Testing on your layout. Define this portal filter:
IllnessPhase = "Acute" AND SampleType = "Serum"
Put the DateCollected field in this portal and it will show the first related record for the filter specified Phase and Type. This is a "display only" way to show that date. Other methods will be needed if you want to use that date in a script or calculation.
If IllnessPhase = "Convalescent" AND SampleType = "Serum", THEN Serum2Collected = "Yes" AND Serum2Collected Date = DateCollected
The same one row filtered portal can be used to show the Serum2Collected Date. To show "yes", define a calculation field, cPhaseType, in Human_Testing that combines IllnessPhase and SampleType such as: IllnessPhase & " " & SampleType. Define a calculation field in Human_PatientInfo like this:
If ( Not IsEmpty ( FilterValues ( List ( Human_Testing::cPhaseType ) ; "Convalescent Serum" ) ) ; "yes" ; "no" )
Thanks so much for your help. I'll see what I can come up with.
I wrote the SQL Query (see below), but it is asking me to specify an ODBC Data Source (.dbf, .xls, .mdb, .csv, ..... ). When I choose one of the file formats listed, a popup window asks me to enter my user name and password. How do I get over this hurdle?
SELECT DISTINCT a."StateLocalPublicHealthLab", a."CDCLab", a."CommercialLab", a."TestFacilityType", a."Serum1Collected", a."Serum1CollectedDate", a."Serum2Collected", a."Serum2CollectedDate", a."CSFCollected", a."CSFCollectedDate", a."S1DENVCollected", a."S1DENVCollectedDate", a."S2DENVCollected", a."S2DENVCollectedDate"FROM "Human_PatientInfo" aINNER JOIN "Human_Testing" b ON a."Human_PatientInfo_pKey" = b."Human_Testing_fKey"WHERE b."IllnessPhase_AcuteConvalescent" = ? AND b."SampleType" = ?ORDER BY b."SpecimenNumber" ASC, b."IllnessPhase_AcuteConvalescent" ASC, b."SampleType" ASCThe "?" marks were replaced with "Acute" and "Serum"
If you are being asked to specify an ODBC data source, you are using the Execute SQL script step. I am describing using a Calculation field that uses the ExecuteSQL function.
I wrote the SQL Query (see below) into a calculation field. This calculation field lists all of the fields listed next to the 'SELECT DISTINCT' command. The fields are separated by commas.
What do I do with this list?ExecuteSQL (" SELECT DISTINCT a.StateLocalPublicHealthLab, a.CDCLab, a.CommercialLab, a.TestFacilityType, a.Serum1Collected, a.Serum1CollectedDate, a.Serum2Collected, a.Serum2CollectedDate, a.CSFCollected, a.CSFCollectedDate, a.S1DENVCollected, a.S1DENVCollectedDate, a.S2DENVCollected, a.S2DENVCollectedDateFROM Human_PatientInfo aINNER JOIN Human_Testing b ON a.Human_PatientInfo_pKey = b.Human_Testing_fKeyWHERE b.IllnessPhase_AcuteConvalescent = ? AND b.SampleType = ?"; "" ; "" ; Human_Testing::IllnessPhase_AcuteConvalescent; Human_Testing::SampleType )
It's not the query you would need for what you want to do here. Each of your fields in your initial post that return a date or some text based on specific data in your portal's table would be a different SQL query in a different calculation field. Both JOIN and WHERE clauses can be combined to search out a related record with specific values in the IllnessPhase and SampleType fields.
I'm sorry. I just don't understand.
For this one:
If IllnessPhase = "Acute" AND SampleType = "Serum", THEN Serum1Collected = "Yes"
Define Serum1Collected as:
IF ( Not IsEmpty (
ExecuteSQL ( "SELECT DateCollected FROM \"Human_Testing\"
WHERE Human_Testing_fKey = ? AND IllnessPhase = 'Acute' AND SampleType = 'Serum' " ; "" ; "" ; .Human_PatientInfo_pKey ) ) ; "Yes" )
For each of the other fields, it's a different query and in some, you just need to Select for the correct Date field and in others you'll need to use an If function like this as you just need to know if there is any data returned by that query before displaying a value.
I made the Serum1Collected field a Calculation Type. I stored the following calculation and checked that the "Calculation Result" is TEXT.If ( IsEmpty ( ExecuteSQL ( "SELECT DateCollected FROM \"Human_Testing\"WHERE Human_Testing::Human_Testing_fKey = ? AND Human_Testing::IllnessPhase_AcuteConvalescent = 'Acute' AND Human_Testing::SampleType = 'Serum'" ; "" ; "" ; Human_PatientInfo::Human_PatientInfo_pKey ) ) ; "Yes" )
The above calculation stored in the Serum1Collected field still does not produce a "Yes" for the field. What do you think I'm doing wrong now?
Thanks for helping me.
Your query syntax does not match mine. WHERE Human_Testing::Human_Testing_fKey = ? is not valid syntax for a FileMaker SQL Query.
Since the query only references a single table, you shouldn't need to include the table name with the field name, but if you did, you would separate the table and field names with a period instead of two colons.
I would also suggest first setting up just the ExecuteSQL part of this calculation and inspecting the results returned before putting it inside the IF function. That way, you can tell if the query is working before you try using the IF function to return the text "yes".