Consolidating portals and moving data across more than two relationships

[beju0506]

Hello, I'm trying to see if anyone can help me with this.

I'm working on an asset-management database with three major tables and two joins:

Major Tables: Faculty, Computers, Software

Joins: FACtoCOMPUTERS, COMPtoSOFT

The working relationship is that: Faculty can be assigned computers, computers can be assigned faculty. There can be multiple computers per faculty and vice versa. Same with software and computers, i.e. multiple computers to a software, multiple software to computers. Multiple licenses of software are represented by multiple records in the join (so on the portal in computers, it shows as two distinct records).

The issue that arises is that we want to be able to have a report that shows all of the software assigned to the computers assigned to faculty. But that's not all. We want to have it grouped on the report to show just one instance of each software with a counter telling the number of licenses used.

 

The key fields in the db are:

Faculty table: userID

Computer table: compID

Software table: softID

 

The join tables contain each of the "joined" tables' key fields and an autoenter serial number (for uniqueness of multiple records with the same info - multiple instances of software license, etc)

 

Does anyone have any idea how we would do this? We can show the software in a portal in the faculty table, but it doesnt group it. (and my supervisor wants it as a report- nice and neat) When I try to do a report, it doesnt work because if the repoirt is in anything but the comptosoft join table, it wont show multiple records from the other tables. If we put the layout in the comptosoft join table, it wont get the right records from faculty because the relationship from computers to faculty is one to many.

 

We're using FM Pro 8 Advanced...

 

Thanks in advance for your help, we're all going bonkers over this.

Bad Idea!

[beju0506]

Alright, I found a cheap workaround for this:

 

I set a script that runs via a button.

It iterates through the portal line by line and copies the computer ID numbers to the report window and does a find for the first number and then an extend found set( ) for the rest, then does the report. Kinda messy, but it works for now.

If anyone knows a better way to do this, I'd appreciate it.

I'm also trying to get the faculty name over in a global variable but not sure how to put it on the report.

Also, (one last thing I promise ) is there a way to export a report to a text file?

 

Thanks again!

 

Justin

[Ender]

Welcome Justin!

 

You can use a value list based on a related field and a ValueListItems calc to show the Faculty from perspective of the Computer_Software join, or to show the Software Titles from the perspective of the Faculty_Computer join. Then put it all together in the respective join table.

 

See the attached example.

[beju0506]

Thank you! That worked really well! I don't completely understand how it relates them, but it works the way we want, so I'm not complaining. One thing we still need to do, and I don't know if this is possible or not but is there a function to copy text (as in the report) in order to paste it into a text field? I know you can copy a preview and paste it into a container as a graphic, but can you copy it as text?

[Ender]

You can copy field values, but in a normalized system, you shouldn't need to copy data fields from one location to another. It's usually possible to use relationships to show the related values (or aggegate calcs) as needed.