Scripting a Constrained Search?

[vwgtiturbo]

Forgive me if the terminology used in the title is incorrect, I'm still getting familiar with this beast...

In a nutshell, I am taking measurements on a piece of equipment. I am recording the equipment serial number, date, the increment being measured (about 50 different values, from a value list based on a table), then 10 measurements for each increment. To avoid having the date and equipment serial number stored 500 times for each 'measurement session', I have those items split apart into separate tables, then all of the measurements stored in a junction table (see attached, items are renamed a bit to prevent interfering with actual database tables):

I have a report based on the junction table (blue above) and it simply lists all values in the table. The user can search for a particular equipment serial number, but this returns all values related to that serial number (and all of the dates that measurements were taken). Certainly, the user COULD search for the particular equipment desired, then view the available dates, the modify the find to only return those dates. However, I was hoping to somehow script this action, such that the user could simply select the equipment serial number, and only the last 'session' measurements would be returned (the most recent date) automatically, as those are the only pertinent values.

I've experimented with various script steps, and have zero luck. I'm hoping not to have to change the structure, as there is another area of the database that is now working as intended (after days of trial and error). I'm just not sure if I'm missing something really basic (overthinking things, as usual) or...?

Thanks in advance for any insight!

[comment]

2 hours ago, vwgtiturbo said:

However, I was hoping to somehow script this action, such that the user could simply select the equipment serial number, and only the last 'session' measurements would be returned (the most recent date) automatically

If I follow this correctly, you could search the measurement_session table for the (related?) serial number, then omit all found records except the last one.

 

--
P.S. I am not sure what "increment" means in this context. Wouldn't "parameter" be a better choice?

 

Edited July 5, 2017 by comment

[vwgtiturbo]

On 7/5/2017 at 9:53 AM, comment said:

If I follow this correctly, you could search the measurement_session table for the (related?) serial number, then omit all found records except the last one.

 

--
P.S. I am not sure what "increment" means in this context. Wouldn't "parameter" be a better choice?

 

Hmm... I think the part that is tripping me up is that a user would ideally search for the serial number first, but from what I've read on related searches, the report has to be based on the most 'child' table (which I assume, in my case, to be the table with all of the actual measurements listed). I think that the design of the tables themselves might not be helping me, but it seemed like the most logical (to avoid repeating data, space considerations, etc.), considering that the equipment serial number isn't located in the most child table.

The increment is literally an 'increment'. So, when the measurements are done, date and equipment serial number are recorded, and the observer sets an adjustment (ranging from .5 to 2.5, in .05 increments) then there are 10 measurements recorded for each increment (e.g. set adjustment for .50, measurements 1 through 10; set .55, measurements 1 through 10; set adjustment to .60, measurements 1 through 10, etc.).

Initially, everything was jammed into one table. So the date and equipment serial number were repeated hundreds of time, for one measurement 'session'. It just didn't seem right.

Maybe I'm just not looking at this the right way...

[comment]

8 minutes ago, vwgtiturbo said:

a user would ideally search for the serial number first, but from what I've read on related searches, the report has to be based on the most 'child' table (which I assume, in my case, to be the table with all of the actual measurements listed).

One does not contradict the other. You can find the latest measurement session, then do Go to Related Record[] to produce the report from the measurements table.

[vwgtiturbo]

My apologies for such a late response. After reading the last reply, I decided to do a bit of reading so I wasn't a burden asking basic questions. Needless to say, I'm not sure if the design of my relationships/tables isn't conducive to what I'm trying to do, or if there is some basic principle that I am looking past (likely the latter).

By reading this statement "You can find the latest measurement session, then do Go to Related Record[] to produce the report from the measurements table.", that would work in some cases, but I was hoping to go the other way. The method mentioned would show a list of dates to select from and Go to Related would show the resulting Equipment IDs and Measurements. What I'd like to do though is select the equipment first (as that is the most important aspect that we need to narrow the data by), then return all measurements under the LAST session date for that particular equipment.

Going through the Report creation tool (an untold number of times), I'm not sure where I'm going wrong; maybe the terminology in creation doesn't suit my use case or I'm including too many grouping fields, (or not structuring the resulting layout properly), but I usually end up being able to successfully search a particular piece of equipment, but then ALL dates (and therefore, ALL measurements, even those out of date and no longer relevant).

I experimented with setting a global field (the user selects the equipment serial number) for the Find operation, and never was able to make it work (not sure if that was even necessary, but I was trying anything). In all fairness, however, I've never had a global field/variable work, so that it obviously something I need to study and learn...

[comment]

1 hour ago, vwgtiturbo said:

What I'd like to do though is select the equipment first (as that is the most important aspect that we need to narrow the data by), then return all measurements under the LAST session date for that particular equipment.

That is what I understood from the beginning, and it is exactly was I suggested.

Try this procedure manually first, before scripting it (except for the Go to Related Record part, which cannot be performed manually):

1. Go to a layout of Sessions. For the purposes of this exercise, place the serial_number field from the Equipment table on this layout.

2. Enter Find mode, enter a serial number of an equipment into the serial_number field and perform the find. You now have a found set of all Session for the selected equipment. 

3.  Go to the last record. Assuming your records were entered chronologically, that is the latest session of the selected equipment - otherwise you'd have to sort them by date before doing this step.

4. Now you need to do Go to Related Record [ From Measurements; Show related only (match current record only); Using your report layout ] to get a found set of all (and only) measurements taken at that last session.

 

Edited July 14, 2017 by comment

[vwgtiturbo]

Using the same layout that I use for data entry to practice this (based on sessions, with the portal), the process works well (select equipment s/n from the pop-up normally used during equipment selection upon input, view the number of returned records, omit (total - 1) since all are entered chronologically). I never could get this to work with a report based on the Measurements table (from my reading, it seemed to me that reports should be based on the most child table, which, with my understanding, would be the Measurements table).

So... I suppose I can duplicate this layout including the use of a portal (but cut out the superfluous elements not necessary when viewing/printing the information), then use a Summary field in the Sessions table to count the number of dates returned, and after the user selects the equipment to search for, omit (count - 1), or am I thinking of this entirely the wrong way?

[comment]

I am afraid you have lost me completely at this point.

I suggest you have a look at the attached file, which has a structure similar to yours (reduced to the minimum necessary to demonstrate the principle). As you can see, it shows the latest measurements of the selected equipment - and it shows them using a layout of the Measurements table.

 

LatestMeasurements.fmp12

[vwgtiturbo]

You are amazing, thank you tons for the sample! After reviewing this file thoroughly, I came to the conclusion that I was looking at this all wrong. I was trying to use the 'New Report' aspect of FM, and I think that the grouping options and such just confused me (I could never see the results that I had my mind's eye). It seems that working backwards (trying to use the report to filter the data, versus filtering the data then reporting on the end 'found set') complicated matters (especially with regard to the grouping options I was setting up). Nothing would sort as expected, etc.

In any case, beyond straightening my thinking, I now know how to utilize global fields :-) I don't know why I've never gotten that to work correctly... All I need to do is figure out how to get the Increment worked into this (and sorting them), and figuring out print presentation, then all will be well. Your file gives me a great start!

Thanks again for the help! I am SO ready to get this section of the DB done; I'm tired of looking at the same area and am ready for a change. Light at the end of the tunnel, so to speak...

As a side note, the script step for going to the Sessions layout never would have crossed my mind; I couldn't wrap my brain around it ("I don't want to go to that layout! Oooohhh..."). Light bulb eventually went off, but I never would have thought of it. Thanks again!