Match field does not get all records

[gwilson188]

Hi,

I'm having a problem with getting all the linked records I should see in a Portal between 2 tables in the same file. The Match field is of the form MA-0107.00 and is indexed in both files. I have sorted each table independently by this field to ensure that there are not extaneous spaces or formatting would preclude a match, and the value does exist in both files. The "master file" may have several instances of the value, while the "lookup" file has only one entry per value. I get about 80% hits, so not sure what is happening. I cannot find anything odd about the field in either file on the missing ones. I am using FileMaker Pro 7v2.

Thank you for any help,

Gloria Wilson

[-Queue-]

Have you checked for extra spaces or carriage returns at the end of the field?

[MoonShadow]

One way to tell for sure ...

Create a calculation (number) with:

Exact ( text1; text2)

Carriage returns, spaces - any difference at all will produce 0.

[gwilson188]

Thanks for the idea on the calculation, I will try that. I did see some variations in resulsts when I deleted all the Indexes and went back into the linked tables and allowed create as necessary. Still some missing connections, though. I had checked leading, trailing and imbedded spaces manually before I asked the question here. Is there a preferred setting on the indexes? When I used to use MSAccess, I had to define the type of relationship, i.e. one-one, one-to-many, etc. I don't see any way (or necessity?) in Filemaker. Could that be an issue? The indexes seem like they could be a problem, only because I do need to periodically delete all the records and import new sets. Is there a procedure I should be sure to use when I do that?

Thanks for the help!

[gwilson188]

OK, I did create the calc field to check for matches and found problem records that DO match but do NOT display in the portal?? The tables I am linking for the portals are all in the same file ... it is about 156K, so not too large. Could someone look at it and tell me what I am doing wrong?

Thanks!

Gloria

[-Queue-]

Sure, Stuff or Zip the file and attach it to your post. Tell us which table/layout/field has the problem.

[gwilson188]

Thank you! This is a test file for checking a Medical Inventory that is currently maintained in several files in Excel. I update the tables periodically when data input changes. I am trying to audit accuracy and distribution of items. The Layout I am troubleshooting is MedInvBoxes Fema/LA and I am trying to display items from the several tables included in the database. I have some confusion on whether the indexing is correct, and what procedures I may need to redo when I update data by deleting all records and re-importing from Excel files.

Thank you,

Gloria

MedInvTst.zip

[-Queue-]

You seem to have portals, with no relationship associated, covering the FemaMstrCln fields. Remove them and your data magically appears. If you are only interested in one related record's data, then a portal is unnecessary anyway. If you want to show more than one related record (if there are any), change the portals to use the FemaMstrCln relationship and add a scrollbar to them.

[gwilson188]

Queue,

Thank you for taking time to look at this for me. I did as you suggested and everything does link properly. By way of clarification, I just got the FemaMstr done and the CatalogMstr is the only file that has a one to one link for Fema# and ID#. I changed the relationship on the Fema File from the Boxes table to the CatalogMstr and removed the portals as you suggested. I think where my logic got muddled was "blindly" using portals, as I had done to look into the packs tables that may have had multiple records to view, when I didn't need to.

Thank you again for your help, I can get on with my audit now!

Gloria