Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About vwgtiturbo

  • Rank

Profile Information

  • Gender
  • Location
    Lincoln, CA
  1. Scripting a Constrained Search?

    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!
  2. Scripting a Constrained Search?

    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?
  3. Scripting a Constrained Search?

    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...
  4. Scripting a Constrained Search?

    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...
  5. 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!
  6. Learning Scripting?

    Excellent, thanks for the nudge! It's baffled me for months seeing some of the solutions that folks come up with, and having zero idea how they derived their method. It'll still be frustrating, taking me much longer (work and school), but at least I'll have something to work on in my sparse free time.
  7. Learning Scripting?

    Hello, In looking to make my database perfect, it has become obvious that I am going to learn Filemaker scripting. While I can Google and ask questions all day, I'd really like to learn how to do it myself (not so much the raw basic FM functions, but custom functions like 'case' etc.). As an example, with an old FM database, I had downloaded a script that would insert a decimal after I entered numbers in a field (for recording measurements). As an example, if the field entry was 'a' characters long, the decimal would be applied at position 'b'; if it were 'x' characters long, the decimal would be placed at position 'y'. While I used this downloaded script (along with others), it occurred to me that it would be so much better if I actually understood the script so that I could tailor it to other use cases or built completely new variants on my own. My stupid question for the week... where on earth would I even start with trying to learn this? I'm not even sure of the terminology that I should search for in an attempt to learn this. I'll admit that I also have to learn the logic behind these things (in addition to the actual functions and syntax). I just have, what I think, excellent ideas for my database (especially since I will not be the only one using it, so I'd like to make it easy and powerful for the other users), but am realizing quickly that I need more knowledge to do that. Thanks in advance for any push in the right direction!
  8. Export Structure (Tables and Fields) to...??

    I had thought about this, however, during the build of the db, I would end up doing some of the structure, then test it via the design... more structure, more testing... after finally getting everything working the way I am intending, to do this would be a huge step backwards. Thanks though!
  9. Export Structure (Tables and Fields) to...??

    That looks interesting as well, thanks! I just may have to explore that for future use.
  10. Export Structure (Tables and Fields) to...??

    Hello Lee, that was an excellent link, thanks! On my Win machine, I get the 'DDR file missing' error (and exporting via XML is nearly useless). I'll try on the Mac. Thanks again! EDIT: As expected, the process works as expected on the Mac. The DDR is MUCH more interesting than I thought! Talk about handy... Thanks again for the help ;-)
  11. Hello! Being an FM novice, and just finishing the structure of my excruciatingly complicated database, I have to wonder... Is there way to export a list of tables and the fields within each, so I can be sure to include everything on my layouts? I created the layouts a while ago, but then had to redesign quite a bit, and only some bits were auto-added. I just wanted to have a 'checklist' per se, so that I don't have surprises when I actually sit down to USE the beast. I know I could simply expand all of the tables in the relationship graph and print to PDF, using it as a guide, but just wondered what other options there were. Thanks in advance for any insight or personal tips!
  12. Keep Data in Table After Relationship Change

    Wow... Funny how reading that made things a bit more clear. I swear, I must've suffered from tunnel vision when going through this. I'll be modifying the design a bit and see how it changes the behavior. I really hope that my more complicated issues are taken care by this as well (or at least, made a little more manageable). Thanks for the wisdom!
  13. Keep Data in Table After Relationship Change

    Hi Steve, sorry for the late response; I thought I had answered this, but confused it with another question on a different forum. I think I may have figured it out... for now. It all boiled down to my relationship. I was creating a table with each piece of equipment in the inventory, and one of the fields was its location, related to a location table in a one-to-one manner. This ended up being a bad move, as the equipment can move locations over time, so it likely should be a many-to-many (using an intermediary with one-to-many). In this manner, the junction table (EquipmentLocation) would have many records over time, with each piece of equipment's movement to another location filling a new record. I'm still wrapping my brain around it... To answer your questions (not point by point, but operationally...). Each Flight is a mission that uses a particular aircraft, and a particular piece of equipment. The mission flies over a particular area, uses the equipment to gather data, then returns to home location. The complexity comes in... each mission has a home base ('location') and each piece of equipment has a home base ('location') and these are related, such that when a mission is departing from location X, only the pieces of equipment at location X are considered for the mission. What I ran into earlier was: if I send a piece of equipment to another site (i.e. it no longer belongs to me) and then update that equipment's location in the DB, all of the previously created missions from my location that used that particular piece of equipment are now no longer related (such that Filemaker resorts to changing the equipment's 'serial_number' on my layouts to the record number of said serial number). The solution (at least, in my VERY novice mind) was to move each equipment's location to a third table, such that each equipment location will be stored forever (every location that the equipment has been) which should keep the equipment related to any particular location forever (and Filemaker will always be able to show the serial number instead of the record number). I think everything was the result of a simple relationship error; my thinking was that each equipment can only have one location at a time, therefore, one to one; reality says that equipment can have many locations over time (and locations can have many pieces of equipment at any given time) so a many-to-many (and junction table) is likely more proper. The scary thing is that, early in the project, my Flight/Mission table was pulling from a separate value list to allow storing the equipment serial number as text (instead of the record ID) in order to avoid the 'location change/unrelated' issue, and I had an odd second occurrence/relationship set up to 'translate' the text serial to record ID for a relationship further down the line. And for a bit, it worked. Unfortunately, as I dug deeper into the project, other areas that relied on that relationship wouldn't work, so now I'm back to trying to do it the right way (which is a challenge for me, considering it is my first 'proper' database). In any case, for now, I think the relationship was the issue, but if you think that I'm going in the wrong direction, please feel free to correct me! As stated, I am rather new, and looking to learn ;-)
  14. Hello, I have a 'data retention' issue that has been driving me absolutely insane over the past two days. It is actually a multi-part issue, but rectifying the issue in this post may solve the others (so I'll keep it simple... for now). In an nutshell, I have 3 tables related by a locationID. Location locationID Equipment equipmentID serialnumber fk_locationID FLIGHT flightID fk_locationID fk_equipmentID The FLIGHT table is the main data storage entity; Location and Equipment tables just provide data to fill FLIGHT table. While Equipment stores the current location of a piece of equipment, FLIGHT also stores the location of the current flight (the flight's origin). There is an Equipment value list where I choose the equipment used on this particular flight, that consists of Equipment::equipmentID (first field of value list) and Equipment::serialnumber (second field of value list), with only the 2nd field (serialnumber) being visible. This value list only shows the equipment that is related by the Location table (locationID). Relating FLIGHT and Equipment via location allows me to select from the equipment that is actually on-site (i.e. if I am in CA, the value list will only show Equipment that is also in CA, versus listing all of the Equipment, to include those pieces at overseas locations). The problem comes when the relationship is broken (e.g. in the future, due to Equipment movement, I will go into the Equipment table and change the fk_locationID to reflect the Equipment's new location). After this occurs, pop-up menus show only the record number of the equipment, instead of the serial number that I have set up in the value list. Researching this, I did find a 'solution' that entailed creating a second value list that didn't use Equipment::equipmentID (it only used one field, using Equipment::serialnumber), and using a FLIGHT field that I renamed to 'equipmentserialnumber' (since it now stores the serialnumber instead of equipmentID). To make the location realationship work, it was necessary to relate FLIGHT::serialnumber to Equipment::serialnumber, which creates a second Equipment occurrence (FlightEquipment, for example). The problem I am having (and it is likely a rookie mistake, admittedly) is that, for other (more in-depth) areas of my database, I am having a helluva time relating other tables to the Equipment table through the Flight table (to gather only information about equipment used for a particular flight, for example). I'm nearly convinced that it is due to the serialnumber/location relationship workaround, as these items weren't an issue prior. Do I have any other options to keep the Equipment::serialnumber visible in FLIGHT, after the relationship is no longer valid? I've spent two days wracking my brain on this, and being wet behind the ears on DB design, it's driving me insane (for reference, this project is the result of watching many DB design lectures, and attempting to convert an old flat-file Filemaker database to a 'real' relational database. The original intent was to use Postgres, however, I am not the only person that will be using this, and I don't have the knowledge necessary to ensure that it would be successful). Thanks in advance for any insight! I apologize in advance if the explanation is unclear; sometimes it would be easier to simply post a file so others can see what exactly is going on, but with sensitive information included, that isn't always an option).

Important Information

By using this site, you agree to our Terms of Use.