Why am I not getting the data I want through this relationship?

[flutegirl]

I am working on something, and can't seem to figure out why this isn't working.

My solution uses two files - the data file and the UI file. I've connected the two by adding the data file as an External Data Source (Filemaker).

What I am trying to do is have my UI file go to the data file and give me the sum of matching records. However, when I use the product name field as the match, I am getting results, but if I attempt to switch to the product type or product category, I get no results.

The data table is called Line Items. In it is stored (along with some other data) the line item ID#, the product id and product name, the quantity, unit cost, extended cost, and date the unit was shipped.

I have a relationship set up from the UI file where:

UI File::Item ID = Line Item::Item ID

AND UI File::StartDate ≤ Line Item::Ship Date

AND UI File::EndDate ≥ Line Item::Ship Date

I then have a calculation field in the UI File that goes through the above relationship and gives me Sum(Line Item::Extended Cost)

The above relationship works.

However, when I try to change the first line of the relationship to

UI File::ProductType = Line Item::Product Type

things break.

the Line Item::Product Type field is a calculation field that is looking at the value of a global field in another table (the lookup value), and returning either "All" if the global field is blank (because that means I want to see all product types), or the Product Type of that item (from the Inventory table) if there is a value.

I have double checked and the Line Item::Product Type calculation is set to text, I have a relationship in the data file from Line Items to Inventory (which is working properly, as it's returning the proper results for the Line Item::Product Type calculation field), and have checked and re-checked the relationship in the graph from the UI file, and I cannot figure out why this isn't working.

Is there something I'm missing here?

[Edward McPike Jr]

Hi Flutegirl,

I believe it is because the Line Item::Product Type field is unindexed (since it refers to a global field). The left side of your relationship can be unindexed, but the right side has to be indexable. Do a test and create another Product Type field that is Text only, then replace its contents for all records with the original Product Type field. Point your relationship there and see if it works. It should.

I'm assuming some things here:

1. The UI::ProductType field is a global you're using to filter what you're viewing
   
2. The Line Item::Product Type field is the calculation that refers to a global in the Inventory Table.
   

I don't really understand why your Line Item::Product Type field refers to another global. I would think it would simply refer to the product type selected.

If the data of your Line Item::Product Type doesn't change, I'd suggest changing it to a Text field with either an Auto-Enter Lookup (pointing to the same field as your calculation), or an Auto-Enter Calculation, using the same calculation. That is a way to make the relationship work.

I hope this helps. Good luck!

Regards,

Ed

[flutegirl]

I'm assuming some things here:

1. The UI::ProductType field is a global you're using to filter what you're viewing
   
   
2. The Line Item::Product Type field is the calculation that refers to a global in the Inventory Table.
   

I don't really understand why your Line Item::Product Type field refers to another global. I would think it would simply refer to the product type selected.

If the data of your Line Item::Product Type doesn't change, I'd suggest changing it to a Text field with either an Auto-Enter Lookup (pointing to the same field as your calculation), or an Auto-Enter Calculation, using the same calculation. That is a way to make the relationship work.

Thanks for the assistance.

As an explanation of the global fields, here's what I'm trying to do:

In the UI file, I am trying to create a report generator. The way I'm attempting to do that is I have a table (Report Data) which has a field for the Product Type(s) I want reported on, as well as fields for the results of the relationship lookup. A second table (Called Globals, but just contains a single record and is connected to the other table occurrences by the [ X ] relationship I can never remember the proper name of) contains the date ranges I want to compare.

My script is supposed to loop through the records in this table and, by way of the relationship I'm having issues with, return the sum of sales over the various date ranges requested and enter those values into the fields in the Report Data record for that Product Type. As I was having issues directly passing the Product Type name and the date ranges to the Data file via the relationships, I've instead created a script in the Data file passes the Product Type via a script Parameter, and then uses Set Field to populate the global field in a Lookups table.

The Line Items::Product Type is then evaluating that Lookup::Product Type field to populate the field with either "All" or the Product Type, as a blank value in the UI file means I want all values, not blanks.

I tried changing Lookup::Product Type to a non-global (like I have above for Report Data) and it still didn't seem to work, so I took the calculation out altogether, and it is returning the results I would expect.

As to why I wanted that to be a calculation field, for this specific report it's not needed, but I was trying to make this robust enough that I can use the same general framework for the next report I need to do, in which I want to be able to select values for any (or all) of six different categorization fields and have the report locate matching records to do a sum. The problem is that if I select a value for only one field, I need all values of the other five to also match.

I was attempting to do this by putting the search criteria in a global field, then having calculation fields in the Line Items table which would test the global field, if blank, enter "All", otherwise, enter the correct value for that item. This would happen in the UI file as well. I would then have a relationship that is checking against all six fields to find the related records so I can get a sum of the sales value.

Maybe there's a better way to accomplish this relationship? At least I can take care of my current need by simply looking up the data on all records, since there will only need to be a single match field besides the date.

Thanks again for your input and assistance.