AnsweredAssumed Answered

"complex" query question / call for assistance

Question asked by JCMendler on May 19, 2011
Latest reply on May 23, 2011 by philmodjunk


"complex" query question / call for assistance


long term user of fmp, comfortable with relational db's, but no formal computer edu; still using fp7...

A) I hope I can clearly explain my issue, B) I hope someone has a few minutes to help out... ;-)

I am trying to figure out how to set up a data retrieval issue on a very large database (government issued nutritional info). All relationships set as instructed, data imported from the issued ASCII files, no problem. Simple (one match field) look ups working fine. BUT, I am having trouble pulling data from a large table (almost 500,000 records) that actually has two required fields for the look up to match; the problem is that I can only capture the first instance of the match. I do not seem to be able to figure out how to define a second field to pull from the next potential match. That is, I need the data from field "C" after it matches from fields "A" and "B" - a given value for A will have multiple records with unique B's and therefore C's. I am having trouble trying to pull that "next" record with the new"B" instance to get the new "C" data point into a new table.

Field "A" represents a food item, field "B" represents a nutrient, and field "C" the value for that nutrient for that food. For each of almost 7000 food items (A) there are about 130 nutrients (B). I am trying to establish a "report" that would pull all the nutrient values (C) for each nutrient (B) when you plug in a single food item value (A). For each food item "A" there will be several (130) potential field "B's" (each with a unique value, 1-130) in the bulk data table (each A,B,C is a unique record in the source table), that is then associated with the result C. 

I tried to do this directly by starting from the food item table A (only one instance of each value for A, but not with any of the potential B's and C's as part of that table) but I could only pull the first instance of C from the first B when I tried to place a related field (matched by A and B) in the layout. Then I tried to define a new table, with a unique field for each nutrient value (the "C" from the bulk dataset) by matching the food item (A) and the nutrient designation (B). But I still only get the first instance of B and thus C for a given A; I can not seem to get a new field in the same record of the new table to pull from the a different record with the same A value but a new B value to pull a new C data point from the bulk dataset. I then tried to use a portal and WAS able to get the full list of unique data points for C, BUT the only formatting option I could figure out was a scrolling list of portal rows which was NOT very helpful because I needed a second portal to pull the nutrient names and could not directly link the two portals onto one scroll, so I ended up with two independent scrolls - made associating the value with the label pretty much a guess - again there are 130 potential nutrients per food item. 

So, my basic question is... Do I need to better configure / format the portal? Figure out an improved way to define the relationship? Or, in my simplistic view, do I need to go and define 130 new tables for each of the nutrients (B) to pull individually based on the food item A to et the nutrient value C? That just seems dumb and shows an apparent lack of db sophistication - I know it would work, just seems to defeat the point of streamlined data and minimal redundancy. 

Any advice is appreciated. (also, kudos if you understood my question on the first read... ;-)