Report Question (Too complicated to list)

[G Gabriel]

Re: Report Question (Too complicated to list)

Attached a zip with a minor glitch..see below

[Mike Wilson]

Re: Report Question (Too complicated to list)

Rich,
What did you need to repeat? The vehicle information?

[G Gabriel]

Re: Report Question (Too complicated to list)

Done.

[Mike Wilson]

Re: Report Question (Too complicated to list)

Rich,
See if this is closer. I can't figure out why the report has a blank page between each page (I seem to always get this and somehow get it to work, but I can't seem to with this one)

[Mike Wilson]

Re: Report Question (Too complicated to list)

Nice work Gabriel!

Later:
The attached contains correct page report and added the Persons_level to help filter the report.

One of these should work for you. With Gabriel's, you don't have to add any fields or links.

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Sorry Gang, I had to leave the office for a few hours.

Gabe that looks exactly like what I'm looking for, and I can't understand why I couldn't get it to work myself, because you've made it look so simple.

I was putting the vehicle information in the group footer just like you've done, but I was using a sub-report in both the detail section and the group footer. I think my problem was that I was making it too complicated.

Your way is simple, easy to configure, and it works.

As happens quite often, the easiest way to do something, is the method most overlooked!

As always, thanks for everyone's help.

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Hey Gabe, is there any way to get the "Per_Link" (on your example) to happen automatically?

Right now it looks like the user would have to input that link data manually.

[Mike Wilson]

Re: Report Question (Too complicated to list)

Hi Rich,
I believe you have my example and Gabriel's example switched. The per_link is in the example I sent.

To answer your question, yes. The field Per_link is a link that is defined by the vehicle record (Per_level = "03") having the same adm_code as records with Per_level codes = "01" and "02". With the two aspects of same adm_code and Per_Level ="03", the person's Per_link field could be populated with the value of the vehicle's record Per_Code field.

If the vehicle record is entered first, the Per_Link field could be populated at time of the persons record entry (form OnSave or field rule OnSaveRecord event) because the vehicle's record exists. However, if the vehicle record entry follows the entry of the persons records, there is no vehicle record to acquire at the time of person entries. So you would have to have an OnSave form event, or OnSaveRecord Field Rule script that updated all records of that Adm_code that were not Per_level = "03".

In short, there is a way. I hope that makes sense.

[Mike Wilson]

Re: Report Question (Too complicated to list)

Rich,
See if this is what you had in mind for autofill the Per_link field. The script is on the Persons table field rule Record Events OnSaveRecord. It might not be very polished but seems to work.

Hope it helps.

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Hey Mike, sorry for the confusion. You are correct, I was looking at your database and report, but thinking it was Gabriel's.

I looked at the revised edition (Hartnett3), but it didn't seem to auto populate both the vehicle and persons Per_Link field, only the person.

If it makes any difference, the "people" will always be entered before the vehicles, as the tab for that info shows up before the tab for the vehicle info.

(Even though they both use the same table, the tabbed gridlinker makes it look as if they were two different tables.)

[Mike Wilson]

Re: Report Question (Too complicated to list)

I looked at the revised edition (Hartnett3), but it didn't seem to auto populate both the vehicle and persons Per_Link field, only the person.

Rich,
It wasn't supposed to. The direction is linking people to a vehicle... unidirectional. But enough said, because as Gabriel pointed out, that link is not needed at all. You have the administration (parent) linking the people and vehicle as a group and the Per_Level = 03 to identify the vehicle. With those two, you don't need the Per_link. I've attached the example with HAVING REMOVED THE PER_LINK and adjusting to filter for the combined adm_code and Per_Level = 03.

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Thanks Mike, I'll test it out as soon as I get back to work.

Rich

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Mike I was looking at the layout and it does work with your test data, but I think I forgot to mention that my vehicle data is actually on separate fields. In other words in my "Persons" table, there are fields for a person's first name, middle name, last name, address, etc., and then there are fields for Vehicle Year, Make, Model, Style, etc.

In your test case, it looks like you've placed the vehicle info in the same fields as the first, middle and last names of the persons. How would me having separate fields affect the report you've created? Would I still need all of those calculated fields?

Thanks

[Mike Wilson]

Re: Report Question (Too complicated to list)

Rich,

How would me having separate fields affect the report you've created? Would I still need all of those calculated fields?

Yes, because you are having to do a lookup into different records of the same table. I figured you had different fields, but you could sort out the third parameter in the lookups in the report calculated fields would direct to those fields. Attached reflects different field for vehicle info and the change to the form OnFetch and report calculated fields.

[Rich Hartnett]

Re: Report Question (Too complicated to list)

Okay Mike now I understand. I have since taken your design and modified it with the actual field names used in my application and it works, but with a small glitch, and I attribute it to something I've done, but let me run it by you.

I narrowed the report down to one particular Record No. (LinkingKey). (You call it "Admin_Code.") This incident has TWO juveniles and one vehicle. The report is producing three records. The first one has the vehicle information, but no "persons." The second and third are correct, with one juvenile per record, and the vehicle information redundant on each, which is perfect, but in this particular case, the report should have only produced two records.

Here are my calculated fields. Maybe you can see a problem in here:

Note: My "Persons" table is actually called "Persons_Ncpd," my "Admin_Code" field is called "LinkingKey," and "Per_Level" is called "Personlevel.")

vYear = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Vehyear"),"")
vMake = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Make"),"")
vModel = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Model"),"")
vStyle = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Style"),"")
vColor1 = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Persons_Ncpd->Color1"),"")
vColor2 = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Persons_Ncpd->Color2"),"")
vTag = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Persons_Ncpd->Tag"),"")
vTagState = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03",lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Persons_Ncpd->Persons_Ncpd->Tagstate"),"")
vCar = if(lookup("Persons_Ncpd","Linkingkey = "+quote(Linkingkey)+" .AND. Persons_Ncpd->Personlevel =\"03\"","Personlevel")="03","Vehicle Information","No Vehicle Involved")