Find Last Related Record

[javabandit]

This has got to be simple, but I'm new to FileMaker (and database solutions in general). I've been reading this forum for about a year while building my previous databases, but this time I'm stuck. Searching the forum has yielded no answer this time, so I registered and am now posting for the first time.

I have a table (x_numbers) with a primary key. It's related to another table (rfq_routing) using the same value as a foreign key. So, one record in x_numbers has many child records in rfq_routing.

The x_numbers are requests for quote. As the pass from department to department in the company, a new record is added in rfq_routing, which displays as a portal in the x_numbers layout. So, at any given time you can see the history of who had the rfq, and who currently has it.

I want to find all the x_numbers records that are marked "Pending" and then find only the last record in the rfq_routing table. Then, I want to total up the quantity of x_numbers in each department.

I have a "table view" layout that shows records from x_numbers, and shows the employee_name and department from a second table occurance of rfq_routing, where it is sorted backward so that only the latest employee_name and dept display.

Ideally, I'd like to run a script to find all pending x_numbers, GTRR in rfq_routing for the found set, and pick out only the last entry for each x_number. Then, I want to total how many of the related records are in each dept. I want to populate this info into another table report_pending, which is a report of the pending rfq's, who has them, and how long they've been held.

Sorry for the long post. Any advice is appreciated. If I'm unclear, please ask me to clarify.

I think I attached a zip file of my database... hope it worked.

Thanks in Advance.

x_numbers_tmp.zip

[Søren Dyhr]

Each of your related record needs a recordID field as well, then should you study this template to see how to single out the latest:

http://fmforums.com/forum/attachment.php?attid/11728/

Which originate from this post:

http://fmforums.com/forum/showpost.php?post/266487/

When the extra relationship is established could you for the entire found set of parent records issue a GTRR(fs) to get the latest for each!

--sd

[javabandit]

Thank you for the help!

I'm studying the file, and I've been able to replicate the same functionality in my db. I really don't understand, though. And I'm still not quite there.

I did have an ID field in the child table already. So, at this point, I'm now displaying a portal that shows the first record of each Criteria from my equivalent of the Child_cParentID|Criteria_1st table occurance.

So, I get the first record from the Estimating dept, the first from Engineering, etc.

I guess the next step would be to do the GTRR(fs) that you mentioned. What is the fs?

My goal is to find only the last dept for each "pending" x_number, and report how many x_numbers are in each dept.

[javabandit]

Søren (and all!),

With much effort today I am getting closer to my goal. I am using the example file as a means to test and prove out the structure that I need. I have attached a modified version of that file, where I have created a method of attaining the last last child record for all in the found set. (FS = found set, right?)

The "GTRR" button on my layout is giving me the found set that I was after. So far, so good!

Now, I want to be able to count how many ParentID's have each criteria. So, for the example file, I would want to report that in that found set:

Criteria #of Parents

White 0

Green 1

Black 0

Blue 1

So, how would I do that? I'm still thinking and working at it... any more ideas are -very- appreciated!

Modified.zip

[Søren Dyhr]

I can't help seeing your struggle with the concepts as pretty healthy ... but looking at your template didn't my suggestion make completely sense.

What I suggested wasn't necessarily an aggregate function to establish the latest but instead an inversely sorted auxillary selfjoin, making the singling out of the latest - if you take a look at the attached template!

The reasoning behind is that if the span of records are huge is the rendering of the layout be prone to show the evaluation - pure relational matches are faster - disregarding the number of records to deal with.

Now, I want to be able to count how many ParentID's have each criteria. So, for the example file, I would want to report that in that found set:

Criteria #of Parents

White 0

Green 1

Black 0

Blue 1

There is something wrong here a hen/egg issue, or rather a table is missing. Each criteria needs to be an individual record in their own table. It's the only way you can establish that say; "white" haven't been used yet. Establishing this purely on the values seen in the many table, would require that a value-list of possible choices existed instead of a genuine table.

My take here is that you have landed yourself in a classic many to many without seeing it comming?? Study this:

http://www.filemakerpros.com/joinprint.zip

--sd

example.zip

[javabandit]

Søren, thanks for your tips.

I worked several hours yesterday comparing my structure with the example you posted, and I see how the example is working. I was also able to get my database to function with the inverse sort in the same way. This is good. I am learning.

I also studied the joinprint example quite a bit, but I don't see how this applies to my situation.

I still want to generate the report as I wrote about above. I think I might need to do it with a script. The script would have to be something like this:

Find "Pending" parent records

set var dept = first dept

Loop

GTRR Child table

Constrain by dept

set var count = Get foundcount

set field dept in report table

set field count in report table

set var dept = next dept

End Loop

Maybe there's a better way?

[Søren Dyhr]

I would indeed attempt reporting from the most atomic of the tables and not write into a special reporting table, since it's against the gist of relational approaches to have any sort of synchronization of data into two location, all summary data is in the atomic table anyway.

This is more or less what you wish to do here:

I want to populate this info into another table report_pending, which is a report of the pending rfq's, who has them, and how long they've been held.

My take on it, that it's a flawed reasoning and I'm puzzled by your eager to script everything.

Basicly should the scripting only to turn to a layout, perform a sorting and finally turn into previewmode or send it to a printer.

I can strongly suggest you try to report via the subsummary measures already found in the tool, and only if special circumstances doesn't allow you to use straight forward subsummary reporting (could be IWP which doens't provide a previewmode)

Now a little later when I have studied your post a little further, could I see what you might be aiming for, what I then dissagree with is the use of another table for the reporting purposes, just place a field in the main table, unless the scenario is multiusers which then should be dealt with independent records ... but I still fail to see it any better than ordinary summary!

--sd

SansPreview.zip

SansPrevievMU.zip

Edited October 10, 2008 by Guest

[javabandit]

Søren,

You are right. I do not really wish to use another table. Also, I always lean toward scripting because I am more familiar with that general type of 'programming'. I am still learning many things about relational databases.

I have just viewed the video you linked above. Brilliant! I never thought there would be such good info available on YouTube. I will be watching more of them!

I will study your newly attached files this afternoon. Thank you for helping so much.

[javabandit]

YES, YES, YES!!!

It is now working!

I am using the summary field and preview mode as demonstrated in the video, and I'm getting exactly the info I wanted.

I'll report more next week, when I have had the chance to tidy up, and test more.

[javabandit]

Okay,

First, many, many thanks to Søren for pointing me in the right direction.

Now I've cleaned up my database and have applied the principles that I've learned in this process. I'm attaching the revised version of my file for anyone interested in seeing it.

Pressing the "Report Pending" button on the lower right column of buttons will show the report in Preview mode with all the data I wanted to show.

There is one thing that I really wish I could do better. Right now I'm looping, waiting for Get(ActiveModifierKeys) to become true. And, I have a note on the layout to tell the user to "Press Shift to Go Back". This seems messy, and inconsistent from a UI perspective.

I would love to simply have a button "<

I realize I'm offtopic now.

Any tips for a better UI?

x_numbers_tmp.zip