Better way of report scripting?

[Rich S]

Once again, I genuflect myself to the FM gods and I'll gladly place a copy of MS-Access on the database alter as a sacrifice to you.

The attached db is a work in progress, so its navigation between screens is a little rocky. I'm focusing on the LT (long-term) report section for now and will model the other report functions after it.

If you go to the Long-Term_Costs_Report_Switchboard layout, you can see that a user can click on a button to select the desired category report. The first one, Asset Capital Loss (by Date) works fine. The problem is the scripting to create a report: there are two report types: one for xDate (or by Date), and the other xReport (by Report number). As is, the LT_Asset_Capital_Loss_Report_xDate script is coded for that particular category, but with all the categories to create print scripts it would be unwieldy and time-consuming to create a separate print script for each category's xDate and xReport reports.

I'm sure there's a more elegant way of coding the scripts so I only need one layout/script to generate the reports. I was thinking that maybe replacing some of the current fields with global onces in the LT_Cost_Entry_Report_xDate layout would work, but I really don't know if that's the way to go or how to code for that.

This is important for me since I have to have the whole database coded and ready to go by Tuesday, so if I can get a comprehensive response from someone before then, I'll send that person a Starbucks coffee card as a personal thank-you.

Ciao,

Rich

Incident_Report_v1.0a.zip

Edited September 1, 2006 by Guest
added missing word from text

[bruceR]

First of all, do you understand how to do scripted finds instead of "restore" finds?

You use a global or script variable to pass a value to a find script. So for instance you have a global set with a drop down list for Cost Category. Assuming the user has made a selection:

Set Error Capture On

Enter find mode

Set field [Category; gCategory]

Perform Find

If get(foundCount) = 0

Show message "No match dude"

-- back to report menu

else

-- continue report script

End if

See attached.

Incident_Report.zip

[Ender]

Then the second thing that will reduce duplication of effort is to employ sub-summary parts. If you do it right, you can use one Report layout for similar types of reports, and show breakdowns by using a sub-summary part for each. To show the correct breakdowns, you need to sort the found set by the correct break field(s).

I'd probably use one script for these different things, using script parameters or custom dialogs to branch according to the button that was clicked. An example might look something like:

...

Go to Layout [ Report ]

Show Custom Dialog [ "Message" ; "Do you want a listing by Report# or Date?"; "Report"; "Date" ]

If [ get(lastmessagechoice) = 1 ]

Sort Records [ No Dialog ; by Date ]

Else

Sort Records [ No Dialog ; by Report# ]

End If

...

[bruceR]

So who gets the starbucks card? He seems to have disappeared.

[Rich S]

Nah, I've just been VERY busy with work; I've been plugging away at two FM db's this weekend. Somehow, supervisors started hearing about the whiz-bang db I'm throwing together so it's "While you're at it, can you make me this?" I haven't seen daylight since Friday morning and won't until next Tuesday...not that it's bad thing since I have some really ugly neighbors.

Since you both were instrumental in me making headway with the first db, you both will get a Starbuck's card; hey, fair is fair, right? So, if you'll each e-mail me your mailing addresses, I'll pop them in the mail Tuesday. Enjoy, and again many thanks...though I'm still having problems with the db I first wrote about above, so you may hear from me before Tuesday again.

Ciao,

Rich

Edited September 3, 2006 by Guest
missing word

[Rich S]

See! That didn't take long--I have 3.5 remaining fixes that, for now, my atrophied brain cannot tackle...and yes, I'll up the coffee card ante and/or will send one to another poster if he or she is so inclined to help here:

1) If you go to the Report_Switchboard layout, in the bottom right-hand corner, there's a choice for Date Range. If you'll go through the motions the script works, but on the Report_xDate_Range layout--where the script winds up--I can't figure out the calc/script so in the title's header, it'll put in the date range as part of the title, e.g., "Costs in Date Range: 1.01.2001 to 4.4.2004."

2) Probably an easy one: in the Incident_Report_Entry layout, Equipment Involved section, I put a portal. How come, after entering a couple of records there, they don't appear in the portal after they've been entered? The records are created in the related table, Equipment, just fine, so...?

3) I'm stuck on the construction of the Incident_Report layout. I've done form letters before in FM, but nothing multi-page so I think I really messed up in building the layout; don't laugh too hard, okay? Should I sub-summary all the different headings and text, or...?

I need a break after 30+ hours of this crazy thing. I think I'll go wedge a potato up the tailpipe of my Troglodyte neighbor's noisy pick-up truck, set out a lawn chair, and just watch his frustration level go into orbit for awhile... (Should he want to eat a banana, I'll recommend to him to peel it, first.)

Ciao,

Rich

Incident_Report_v1.1b.zip

Edited September 4, 2006 by Guest
Figured out how to import the portal records into the report.

[bruceR]

Thanks. You haven't provided us with *your* email address. You can also set it up as part of your profile.

I see you hard-coded a bunch of different reports. You could have passed script parameters to the main report script also, and the script parameters can be attached to the button.

"2) Probably an easy one: in the Incident_Report_Entry layout, Equipment Involved section, I put a portal. How come, after entering a couple of records there, they don't appear in the portal after they've been entered? "

Because you based the relationship on ReportID = EquipmentKeyFieldID. ReportID holds values like 00002, EquipmentID holds values like EQUIP00001. So of course they don't show up.

Depends on what you are really trying to do with equipment - do you really want a table of equipment where there is just one record per real physical piece of equipment?

Ignoring that question, what you want to do is to turn off the autocreate checkbox in the Main side of the relation; and you want to put a field "fkReportID" in tEquipment and use THAT for the relationship.

Edited September 4, 2006 by Guest

[Rich S]

Hello, Mr. Bruce--GREAT to see you! I corrected the oversight about my e-mail address. Sorry 'bout that.

Actually, I did take your advice under consideration and implemented it, but my supervisor said, "That's ugly--go back to the pretty button thing." So, since he's the guy that signs my paycheck...

Thanks for the Report_ID keyfield catch. I saw that about ten minutes before I signed on here. I created a new field in tMain called MAIN_Keyfield, so that way the keyfield is independent from a data field (as it should be). Am I to understand, then, that MAIN_Keyfield should not create a new ID using the Auto Entry function? Should it be a fixed (unique) number, then? ...or should I change the EQUIP_ID keyfield to fkReportID in tEquipment and tie that back to...? (I'm quickly beginning to appreciate the "fk" part of that instruction. :S)

The Equipment table was created so I wouldn't have to resort to repeating fields in the Main table; a previous FM guru said that's the wrong way to go at storing data since it's antiquated. I may be operating of ignorance (actually, I'm sure of it) but I can't think of an elegant way to store items of equipment so they're tied to a parent record other than creating a separate, portaled table for those records. If you can think of a tidier way, I'm all for it!

I'm still attempting to fix the other stuff I mentioned earlier. Time to put on some inspirational music (soundtrack to Star Trek II: The Wrath of Khan) and load up on some fresh coffee.

Edited September 4, 2006 by Guest

[bruceR]

I think you still have some readability problems because of the half-shading crosshatch that you have selected for many objects.

Too bad about your boss's decision. It woud have been MUCH eaiser to maintain using the value lists. And the radio buttons coulda been prettied up some and you could rename the button "Create Report"

Here's another labor saving tip using the method you have selected (separate scripts). You can make the script name a parameter. So each report script would read exactly the same:

Set field [zCategory; MiddleWords(Substitute(Get(ScriptName); "_" ;" "); 3; 99)]

Perform script [Create Report]

That way you only need to duplicate a script and rename it to get it to do what you want.

More comments...

Edited September 4, 2006 by Guest

[bruceR]

Well, I don't know what you're talking about with the Main thing. You already have a Report Number as the primary key. What is the point of creating another primary key?

The equipment table already has the Equpment ID you created and that is OK. You just can't have that field as the relation to tMain - and it seem to me that tMain should really be called tReports or just Reports.

The problem with repeats is a more specific and real problem than being out of fashion. You can't sort, individually delete, omit, summarize-by, or much of anything useful with repeats.

The question about equipment I alluded to but did not deal with previously is that you may want a "real" equipment table. Where one physical piece of equipment has ONE record. And then in your reports you would have a different table, maybe IncidentEqupment. So you might say that equpmentID ABC1 was involved in Incident1; and it was also involved in incident 7; etc.

Right now if you have the same piece of equipment involved in multiple incidents you have to accurately and completely re-enter all its information for every incident.

[Rich S]

Sorry to be such an airhead, but programming is definitely not one of my strongest skills. I'm still reading up on portals and it's making my brain quiver like jello.

I've attached the latest rev of the file so you can see in the Relationships screen what I meant with the Main table (renamed Reports per your suggestion). Am I close this time, or...?

Thanks for the scripting tip--I'll implement that later; at this point, I have to get the file done today so I don't have time to simplify until later.

To answer your question about the Equipment table, what I would like to accomplish is what you're suggesting: put all the involved equipment for each, separate incident/report into one record. Otherwise, as you've said, I'll have 8,000,000 discrete records floating around, which isn't a good thing. How do I accomplish that?

I'm still working on the Incident Report report schema; both ways I've attacked it leave something to be desired. :S The cross-hatching you've mentioned only appears while in Layout mode; in Browse it looks okay. I'll look into that, too.

Thanks again for your time and patience. I picked up your Starbucks card, so it's good to go tomorrow; I may be dense, but I always keep my word. :

Incident_Report_v1.1b.zip

Edited September 4, 2006 by Guest

[bruceR]

You're going to have 8 million records anyway - if that is the real count of how many equipment/incident occurrences thare are. It's just the contents of the record that will be different if it is normalized. Haven't looked at the file yet.

[bruceR]

Argh.

Your keyfield names are confusing. You removed the primary key in the equipment table, which I said to leave in place. Then you went ahead and added a second primary key in Reports! Don't do that. What is "EquipmentKeyFieldID" ? What is with all the "p" prefixes?

Every record in every table needs a primary key so it can be uniquely identified. It will generally also need at least one foreign key depending on which tables are related.

Incident_Report_v1.1c.zip

Edited September 5, 2006 by Guest

[Rich S]

Sorry, Bruce--I was trying to attack the portal problem on too many fronts at one time. MANY thanks for the fixes/improvements--I really appreciate it!

One last problem: when I create a new Incident record with new/different equipment data, the report still combines all the equipment together from all the separate report numbers, e.g., If Report 0001 has aircraft N729FE listed and Report 0002 has N941FX on it, when I run the report generating script it combines the two aircraft instead of keeping them fixed on their own respective report. Is that because I need to insert a Find statement in the report script, or...?

I also created a new table just for the splash screen, agreement page, and Main page (the plane with the three buttons) since if they stayed inside the Report table then they would've been created again for each record. (And no, there's no relationship between that table and any other.)

I promise: no more questions--you've gone out of the way to be helpful already.

Edited September 5, 2006 by Guest
grammar police

[bruceR]

"No more questions" isn't necessary. Glad to help. I did no work on the report scripts and I think yes, you will have to do a find or do subsummaries by Report Number to segregate them. Having the equipment all defined as part of multiple case calculations - ugh.

I also suspect that you will want to make further changes to equipment table. As it is now - with sample data you have posted - you are not really distinguishing between airplanes and equipment. I suspect at some point you will need to make that distinction, as one airplane may have more than one piece of equipment involved in an incident.

But then I really don't know what you mean by equipment. Maybe you mean airplanes; and ground support equipment - various kinds of vehicle that might run into each other. Whereas the word "equipment" makes me think of things installed on the airplane - radios, radar, other things and obviously in that case one tail number can have many pieces of equipment.