Matching one field against another field containing a list of values

[Wodin]

Hi

I'm not sure this is the correct place to post this. If there's a better place, please let me know.

I have a list of items that I need to categorize. I have a table called Category which has two text fields defined. "CategoryName" and "ItemNames".

So each row of the Category table contains a category name and a list of items. The list of items is a carriage return separated list of item names.

So there are no "Value Lists" involved, but the list of items is kind of like a Value List, I suppose.

I have a script that extracts the item properties for all the items in the related set and then builds up a string to send to a web viewer using a URL like data:text/html,[...] for drawing interactive graphs etc. This part works fine (except it could be faster). But now I want to categorize each item based on which category its name matches with.

I could probably use something like PatternCount() to see if the item name is contained in a particular category's ItemNames list and I'd have to loop through the categories checking each one.

Is there a better way? It seems I'm doing a lot of manual work.

So basically in the FileMaker script where I'm building up the string to send to the web viewer, I want access to a list of items including their attributes, and then also a category which is based on matching the item name against the list included in the categories table.

If it makes sense to store the categories some other way, I can change that too.

Thanks in advance.

[comment]

I want access to a list of items including their attributes

The way you describe it, an item cannot have any attributes (other than belonging to a category). You should have a table of Items, where each item is an individual record and can have attributes (fields in the Items table). One of these fields should be CategoryID, and you'd use it to link the item to a category. This is assuming an item belongs to one category only - otherwise you will need a third table to join items to categories.

[Wodin]

The way you describe it, an item cannot have any attributes (other than belonging to a category). You should have a table of Items, where each item is an individual record and can have attributes (fields in the Items table). One of these fields should be CategoryID, and you'd use it to link the item to a category. This is assuming an item belongs to one category only - otherwise you will need a third table to join items to categories.

Sorry, I didn't provide all the details because I didn't think making people wade through a novel in order to answer my post would be likely to get me answers

Here are more details. I hope it makes things clearer:

There is a parent table containing "reports". There is a child table, linked to the parent table, containing the "items". The parent and child tables are linked with a SerialNumber field.

The reports and items both have FirstName, LastName and DateOfBirth fields (i.e. the information is duplicated in the child table). These are used to generate graphs based on certain of the items for a particular person. (Yes, firstname, lastname and DOB are not necessarily unique, but they are good enough for my purposes at the moment. Pretend it's some sort of identity number or social security number or whatever if you prefer )

So a list of reports is shown in an "inbox" with a button for each that will go to a layout with the web viewer to draw the graphs for the person that that report is linked to. But the graph is drawn based on all of the reports linked to that person and not just the particular report with the button that the user clicked on.

When the button is clicked, a script runs that fills the firstname, lastname and dob in a separate table (Graph) that references the child table based on firstname, lastname, dob. The script then calls the Go to Related Record script step to go to the Graph table and show the layout with the web viewer on it in order to show the graphs.

On entering the graph layout, another script runs to get all the details for the items to graph, build up the code use in the web viewer and sets the web viewer's URL to "data:text/html,[the code]".

What I'd like to do is something like:

SELECT itemdate, itemtime, itemname, value, units, reference FROM child

WHERE firstname = '...'

AND lastname = '...'

AND dob = '...'

AND itemname IN ('item1', 'item2', 'item3');

(The "itemname IN ..." clause is because the user will only want to graph certain items at a time. These are specified in the Graph table in separate fields.)

and loop through the returned results building up the string to pass to the web viewer. But it seems that this is impossible in FileMaker for a number of reasons. (Execute SQL seems to be the closest, but doesn't seem to support returning the results of a SELECT statement; only seems to apply to external data sources via ODBC; etc.)

Instead, I am using a custom function that loops through the values in all the fields using GetNthRecord and building up a ¶-separated list of rows. The script then loops through these lists extracting the values and building up the string to pass to the web viewer.

All of this is working fine, except can be quite slow. But now that I have to categorize the items too I'm not sure how best to do that given the above and I'm wondering if the above should be scrapped and redone in a way that requires fewer workarounds or manual labour.

I have glossed over some of the details still. I know the above is more complicated than it should be, but I'm not sure how much is my fault and how much is FileMaker's I hope someone can provide some hints, but I am also thinking about how I can do it in a more sane way by re-examining past decisions/assumptions.

Thanks.

[comment]

I believe you should have three tables:

People -< Items >- Reports

In order to find Items that belong to one person and several selected reports, you can define another relationship as:

People::PersonID = Items 2::PersonID

AND

People::gReportIDs = Items 2::ReportID

where gReportIDs is a global text field, formatted as checkboxes using a value list of all reports.

Another option is to perform a scripted find that creates a request for each checked value in gReportIDs.

Both methods can be implemented even without a dedicated People table. Under no circumstances should you have numbered fields like Item1, Item2 and Item3.

[Wodin]

Thanks very much for your reply. It looks helpful, but I'm having trouble figuring out exactly how to use your suggestions.

I believe you should have three tables:

People -< Items >- Reports

OK, actually it's several selected items from all reports referring to this person.

I think I already have more or less what you are saying above, but I might have misunderstood.

I have a Graph table which contains the preferences of the user who is logged in to the system wrt. which items they want to graph. The Graph table is linked to the Items table (as you call it) by the "PersonID", (where this PersonID does not refer to the logged in user, but to people that the reports refer to.) The Graph table is, I think, what you are referring to above as a People table, but maybe not.

I have the Items linked to the Reports using a SerialNumber field for the relationship as mentioned previously.

The Graphs and Reports tables are linked with a username field for the logged in user. This is a global field in the Reports table and requires that the field is filled in when the user logs in. I'm implementing only part of the functionality of this solution and the main developer implemented things this way. I suspect it's not the best way, but I don't know any better.

In order to find Items that belong to one person and several selected reports, you can define another relationship as:

People::PersonID = Items 2::PersonID

AND

People::gReportIDs = Items 2::ReportID

where gReportIDs is a global text field, formatted as checkboxes using a value list of all reports.

I do have a relationship linking the PersonID from the Graph table to the PersonID in Items, which requires setting the PersonID in Graph before running the script(s). Again, I'm sure this is not the best way to do things.

Using something like gItemNames linked to Items::Name (I need to use the names) might help. Not sure why it needs to be a global field, though.

... I'm having trouble framing my questions correctly, because FileMaker is a bit like a foreign culture to me and in general I don't know the "FileMaker way" of doing things that seem like they should be simple.

e.g. how can I say I want a set of fields from two or three tables that match certain criteria and then be able to loop through them to do something? It seems that a Perform Find script step allows you access to the "current" table's records, and doesn't allow you to reference another table's fields directly? I suppose you could loop through a list of values extracted from the other table and then create Find requests on the fly to look up records in the first table? Again doing stuff manually that should probably not be necessary. I suppose one should use relationships for this then. But how do you access the related records exactly? Using "Go to related record" requires a layout, but what if I don't want to go another layout? I just want access to the data. That doesn't mean I want FileMaker to display it on a layout in any way.

Another option is to perform a scripted find that creates a request for each checked value in gReportIDs.

Both methods can be implemented even without a dedicated People table. Under no circumstances should you have numbered fields like Item1, Item2 and Item3.

For a "scripted find", is that just the "Perform Find" script step? It seems you can't reference another table in the conditions. Is that right? Would you mind elaborating on what you mean by "perform a scripted find that creates a request for each checked value in gReportIDs"?

The "Item1", "Item2", etc. in my previous post do not refer to field names but values in the "ItemName" field in the Items table. (There are actually fields like that, though, created by the other developer, but I won't go into that right now. I fear I've probably confused the issue and myself enough for the moment.)

Thanks again for taking the time to reply.

[comment]

OK, now I am officially confused. Let me clarify something: I wasn't trying to answer your question about graphing (yet). At this point, I am trying to understand what kind of data you track, and whether it's organized properly. The only thing I am reasonably sure of is that it's not.

When you say a Graphs table or a Reports table - it doesn't mean anything to me. What does a record in each table represent in real life?

---

P.S. In a parent-child relationship, it is the child record that holds the ParentID. I get the vague feeling you have parent records holding multiple IDs (or other identifying attributes) of their children.

Edited July 14, 2011 by comment

[Wodin]

OK, now I am officially confused. Let me clarify something: I wasn't trying to answer your question about graphing (yet). At this point, I am trying to understand what kind of data you track, and whether it's organized properly. The only thing I am reasonably sure of is that it's not.

When you say a Graphs table or a Reports table - it doesn't mean anything to me. What does a record in each table represent in real life?

OK, let me try to explain it properly Sorry for not being clearer to begin with, but since I was just trying to categorise some stuff that's otherwise already working I did not want to provide the whole design of the database. For the categorisation I just need some way to match a string against set of lists of strings to see which list it falls into, if any, and mark it as "Other" if it's not in any of the lists. But I think you're right that fixing the organisation of the data will be a good thing, even if the rest is working currently.

The Reports are lab reports for a patient.

The "Items" are test results. So e.g. the amount of haemoglobin in a particular blood sample, or the number of white blood cells, etc.

The each report will have results for one or more tests, but obviously two reports for the same patient will not always contain the same set of tests, even if it is the same "type" of report. (The report might be e.g. a Haematology report or an Electrolytes report, etc.)

So the Report is the parent record with the "Items" or Tests as child records linked by a serial number that is basically your "ReportID". The report has fields identifying the patient, and various other things irrelevant to the graphing. A test has a name, units (e.g mg or ml or % etc.), value, a reference range (which indicates the normal range of values for this test), a serial number (which is the report's serial number) and a copy of the patient's identifying information (i.e. basically a Patient ID). The test also has a date and time that the sample was collected. (The collection date and time and the patient's identifying info should actually be linked to the report and not duplicated in the Tests, because all tests for one report are for the same sample from the same patient, but historically this is the way they have been stored. Also, it means I don't have to worry about the Reports table while generating the graphs.)

There is no separate table with the list of patients, because we just have the lab reports. We don't have an authoritative list of patients. So there is no patient table that is related to the Reports table.

Currently we allow the user to specify 15 tests that they want to graph, so they can specify that the way to graph the "sodium", "potassium", "magnesium", "iron", etc., etc. tests for their patients. These 15 tests are specified in a Preferences table (in 15 separate fields, which as you said in a previous message is not a good idea.) This Preferences table was what I called the "Graphs" table before. It holds various other preferences for the user, but the only thing relevant to this discussion is the tests that they want to graph.

There are far more than 15 tests that can be done, but the user is likely to want to view a certain set of them most of the time. Also, the graphs are useful for visualising things, but are not actually necessary. The users can look at any tests/reports in text form instead of looking at the graphs.

When the user clicks on the graph button linked to a report, it takes them to a layout with a web viewer on it to display the graphs. This is done by putting the patient ID in the Preferences table (for the relationships to the Tests table to show only the tests for this patient). Surely there's a better way than temporarily putting the patient ID in the Preferences table? Anyway, then the script calls Go to Related Records to go to the Graphs layout in the Preferences file. This layout is set to "show records from the Preferences table". I suppose it should probably "show records from Tests" instead? It does work the way it is because of the manual labour described below.

In order to generate the blob of HTML/Javascript to use in the web viewer, the script needs to find the logged in user's preferences for the tests they want to graph, it also needs the "patient ID" whose tests are to be graphed. It then needs to get a list of all tests where the "patient ID" is the one we're interested in and where the test name matches one of the tests the user has said they want to graph.

There is a complication: The tests don't have standardised names or codes, so two tests with different names might actually be the same test and should be on the same graph in that case. (These are line graphs with one series per graph.) e.g. haemoglobin and hemoglobien are the same test. So the user's preferences might contain "haemoglobin¶hemoglobien" for one of the tests, in which case all Tests (for the patient) where the test name matches either of those should form part of the same series on one of the graphs.

Each of the Test1, Test2, Test3, etc. fields in the Preferences table is linked to the Tests table via the TestName field. So in order to draw all of these graphs I need to loop through the relationships getting the set of matching tests. (As I said before, the Test1, Test2, etc. fields were done by the other developer who has vastly more FileMaker experience than I have, but I am not convinced is the best way of doing things.)

I do this as follows:

I first get the number of results using:

Count(GetField($relationship & "::SerialNumber"))

I then get the timestamps (which will make up the X-axis of the graph) using a custom function like this:

getDTFoundSet($relationship & "::CollectDate"; $relationship & "::CollectTime"; $count)

This custom function loops from 1 up to $count calling GetNthRecord(field, recnum) to get the values and returns a string like "timestamp1¶timestamp2¶timestamp3¶timestamp4" with all the timestamps.



(I tried it a different way initially.  I can't remember exactly what I was doing then, but I ran into issues where blank fields caused FileMaker to ignore the field instead of returning a blank string for that row.)



Then I get the Y-axis values:

getFoundSet($relationship & "::Value"; $count)

This custom function works in exactly the same way as the one for the timestamps, except of course that it returns a list of the values instead of the timestamps.

Once I have the list of X values and the list of Y values for a particular relationship (i.e. a particular test), I loop through them building up a Javascript array (in a FileMaker string variable) that will be used to generate the graph in the web viewer.

All this business with looping through relationships and using GetField(), GetNthValue() and the custom functions works, but it seems like there should be a better way. I have left it until now because it does work properly.

Now, I need to add a test category/type to each test. So a "sodium" test would be in the "electrolytes" category and a "white blood cell count" test would be in the "haematology" category, etc. This should also be displayed in the web viewer.

Unfortunately, just as the test names are not standardised, we also do not have the categories for the tests. So the idea is to have a table with two fields, CategoryName and TestNames. The CategoryName would contain things like "Haematology" or "Electrolytes" and the TestNames field would contain the names of the tests that fall into that category.

In the middle of my loops to collect all the data, I could potentially have another loop to go through the categories trying to match up the current test name with the TestNames field, but this just seems like going further down the wrong road. It should work, but it seems that I should rather try to find a way to get FileMaker to do most of this work for me instead of fighting against it.

I hope the above makes sense and explains the issue well enough.

Thanks very much for the time you have already spent answering my previous posts.

[comment]

OK, that's much clearer - though there may be a point or two that I am still not sure about.

I believe the best way to establish the found set you want is by performing a find in the Tests table (and get rid of all the relationships whose sole purpose is to find records). But before I get to that, I'd suggest you add a table of TestTypes with these fields:

• TestTypeID

• TestName

• AlternativeNames

• CategoryID

In the AlternativeNames field, enter a return-separated list of all names that can be used in the Tests table for this type of test, e.g. "haemoglobin¶hemoglobien". The TestTypes table will be related to Tests by matching AlternativeNames to Tests::Name, and to the Categories table by CategoryID. The Categories table needs only two fields:

• CategoryID

• CategoryName

Now, IIUC, we need the user to select a patient and one or more test types - then find the matching records in the Tests table. Is this correct?

The reason I ask is this:

Currently, you have a dedicated relationship for each type of test. Going by my method, you would have a found set in the Tests table, containing ALL tests for the selected patient and selected test types (plural). In order to get the series for each type of test, you would sort the records by test type, then loop inside each group (or perform a series of finds, one for each test type, and loop on the found set before moving to the next type). Either way, the entire logic of producing the charts would be in the script.

[Wodin]

Thanks again for the reply.

I believe the best way to establish the found set you want is by performing a find in the Tests table (and get rid of all the relationships whose sole purpose is to find records). But before I get to that, I'd suggest you add a table of TestTypes with these fields:

• TestTypeID

• TestName

• AlternativeNames

• CategoryID

In the AlternativeNames field, enter a return-separated list of all names that can be used in the Tests table for this type of test, e.g. "haemoglobin¶hemoglobien". The TestTypes table will be related to Tests by matching AlternativeNames to Tests::Name, and to the Categories table by CategoryID. The Categories table needs only two fields:

• CategoryID

• CategoryName

hmmm... I'll think about that, but I'm not sure I see the need for a separate table to map from CategoryID to CategoryName. There will only be a few categories and the names are unlikely to change.

Now, IIUC, we need the user to select a patient and one or more test types - then find the matching records in the Tests table. Is this correct?

Yes, but the user selects up front what tests they will generally want to graph. In general these tests that they want to graph will remain constant.

So when they want to see the graphs for a patient, they just select the patient. They do not, at that time, select the tests to graph.

The reason I ask is this:

Currently, you have a dedicated relationship for each type of test. Going by my method, you would have a found set in the Tests table, containing ALL tests for the selected patient

OK.

and selected test types (plural).

I'm not entirely sure what you mean by this. The first part sounds like you mean all tests for the patient would be in the found set no matter what tests we want to graph. By "and selected test types", do you just mean that I will be able to find what tests the user has selected and then use those in the script to manually filter found set?

In order to get the series for each type of test, you would sort the records by test type, then loop inside each group (or perform a series of finds, one for each test type, and loop on the found set before moving to the next type).

One thing I don't like about the "Perform Find" script step is that if nothing is found it seems to pop up a dialog box asking if you want to modify the find. Is it possible to suppress this so that it just returns 0 results when there's nothing that matches the criteria?

Either way, the entire logic of producing the charts would be in the script.

Well I currently have most of the logic in the script and was hoping that FileMaker could do some of that work itself

Thanks again. I'll have to think about your post and give it a try.

[comment]

I'm not sure I see the need for a separate table to map from CategoryID to CategoryName. There will only be a few categories and the names are unlikely to change.

Well, then you could do with just a value list of Categories, using custom values.

but the user selects up front what tests they will generally want to graph.

It doesn't really matter. They can make a semi-permanent selection in the UserPreferences table. But this selection would be in a single field, using a value list based on the TestName field in TestTypes - hence they can select "Hemoglobin" and not worry about alternative names like "Haemoglobin" or "Hgb".

do you just mean that I will be able to find what tests the user has selected and then use those in the script to manually filter found set?

Yes and no. You will find the test types the user has selected, for the selected patient. You will do this by creating an individual find request for each alternative name of each selected test type + the selected patient. See a somewhat simplified example in the attached file.

One thing I don't like about the "Perform Find" script step is that if nothing is found it seems to pop up a dialog box asking if you want to modify the find.

Use the Set Error Capture[] script step.

LabResultsCharts.zip

[Wodin]

See a somewhat simplified example in the attached file.

Use the Set Error Capture[] script step.

Thank you very much The file and this Set Error Capture tip are very useful.

I'll post back with more questions or to let you know it's solved.

[Wodin]

Sorry for taking so long to get back to you.

I've given it a try and had a close look at the file you posted.

The problem is that I need to loop through the records building up a large string to pass to the web viewer via

Set Web Viewer [xxx; URL = "data:text/html," & $html]

Doing it your way using the built-in charts is fast. Doing it by looping through the records and building up the string takes far longer.

Now I'm thinking that I should try using SQL from a plugin to generate the HTML+Javascript (or at least the array of data in a format that Javascript can deal with).

Otherwise, how can I just dump e.g. CSV format data from the table to a variable (preferably without writing to file and loading it again)?

Thanks.

[comment]

I don't think looping through the records to collect the values should take "far longer". Make sure you are on a form layout and freeze the window for the duration.

Another option is to use a relationship and get the values through the List() function.

[Wodin]

I don't think looping through the records to collect the values should take "far longer". Make sure you are on a form layout and freeze the window for the duration.

Another option is to use a relationship and get the values through the List() function.

I'll look into freezing the window. Not familiar with that. Thanks.

List() would be just what I need, except for this:

Returns a concatenated list of non-blank values...

Why non-blank values only?

I need the fields from all rows, whether blank or not. e.g. I need the dates from all rows, the times from all rows, the numeric result from all rows, the units from all rows, the flags from all rows. The units and flags in particular can be blank. If I use List() I'll get lists of different lengths! So I won't know which unit goes with which numeric result.

That's why I'm using the custom functions.

[Wodin]

Another option is to use a relationship and get the values through the List() function.

Thinking about this some more, it might be best to create a calculation field which concatenates all the fields into one field in the right format and then use List() on the calculation instead of one List() call per field I need.

Does this sound like it might be the way to go?

I'll give it a try and see how it turns out.

Thanks for all your help.

[comment]

Either approach (loop, custom function, list of a calculation field) should work. Since you are running a script anyway, I'd look into a loop first - because that way all the logic is kept at a single location.

[Wodin]

Either approach (loop, custom function, list of a calculation field) should work. Since you are running a script anyway, I'd look into a loop first - because that way all the logic is kept at a single location.

Well, I've been using multiple loops and custom functions and it's slow

I've been trying a calculated field containing the other fields separated by commas. I'll worry about quoting and escaping quotes etc. once I get it working (unless I switch to some other method again). Calling List() on the calculated field is fast. It also cuts out most of the rest of the code. The problem now is how to do the categorisation, and also to exclude certain records that are not really results. (They are in the table, though, and I may not remove them. There are also results that are non-numeric like POSITIVE, NEGATIVE, etc.)

Is it possible to create a calculated field that looks up the category from the TestCategories table based on the test name?

So I have the Preferences table and it links to the Child table (that contains the results) using the Patient ID. In the Child table I now have a calculated field that converts the other fields I need to a CSV format as mentioned above. One of the fields in the Child table is the test name. I'd like an additional field in the Child table (or just another field in the CSV in the calculated field, really) for the category that the test belongs to. The TestCategories table contains a field for the category name and another field for the test name, so it's possible to e.g. find the category given a test name, but is it possible to do something like this in the definition of a calculated field?

I haven't yet changed how the categories work based on the file you posted, but it would be much easier if I could just include the category into the CSV field and use List() to grab that field for all the records.

Thanks.

[comment]

Well, I've been using multiple loops ... and it's slow

There's something wrong here. You can't have too many records to loop through - the chart would be a mess otherwise. And I don't think you need multiple loops - you should be able to get all required data from a record while it's current.

Is it possible to create a calculated field that looks up the category from the TestCategories table based on the test name?

Yes, of course. Just add the related field to your calculation, along with the local ones.

[Wodin]

There's something wrong here. You can't have too many records to loop through - the chart would be a mess otherwise. And I don't think you need multiple loops - you should be able to get all required data from a record while it's current.

Well it's to do with the way I am getting the categories at the moment and excluding the tests I don't want in the results. Also, I'm creating tables in the same way and the speed is only a problem with the table that includes all results.

Yes, of course. Just add the related field to your calculation, along with the local ones.

OK, I must be doing something wrong, because that didn't seem to work. I'll give it another go later. Thanks.

[comment]

Well it's to do with the way I am getting the categories at the moment and excluding the tests I don't want in the results.

If I understand correctly, you are skipping irrelevant records while looping. That is bound to be slow. You should not start the loop until you have a found set of the relevant records.

[Wodin]

If I understand correctly, you are skipping irrelevant records while looping. That is bound to be slow. You should not start the loop until you have a found set of the relevant records.

Yes, but I found that looping through 30000 records was far slower for me, even without skipping irrelevant records, than using List(somecalc) where somecalc is a calculation that adds all the fields I'm interested in to a CSV format string.

So, I'm not sure if I was doing something wrong or if it's just the way things are, but I've found my solution.

Thank you very much for all your help I've learnt a few things during this process and by looking at your sample file.