Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Autocalculating information through multiple relationships?


beju0506
 Share

Recommended Posts

Does anyone know if there is a way to check data a few relationship levels down the line via an auto calculation?

For example, (I've posted about this before on another topic) we have an asset management db with three major tables and two joins.

Major: Faculty, computers, software

joins: factocomp, comptosoft

 

because the relationship goes:

faculty-factocomp-computers-comptosoft-software

this has been causing us some issues.

 

We want to have some sort of relation between faculty and software, so that we can store calculated information about the number of software licenses per faculty. This is also problematic because it is a many to one to many relationship. i.e.

Many computers can be assigned to one faculty and vice versa.

Many software licenses can be assigned to one computer, and vice versa.

 

One idea we had was to have a separate table that is linked to the faculty-computer join and creates a new record when a new assignment is made. It then (through an autocalculation) would check the software name against the list of software in the software table and then check against faculty to see if it was assigned already and if it was, update a counter in the table.

Is this possible? Can someone suggest something a bit less complicated?

 

Any help would be greatly appreciated.

Link to comment
Share on other sites

One thing that I forgot to mention:

 

We already have the information come up in a report, but we found out later we need to be able to tangibly manipulate the data that comes up, so a report doesn't really do what we need.

Link to comment
Share on other sites

I am not sure what exactly do you expect to see.

 

A portal from Faculty to Comptosoft will show you all software installations that are available to a person (alone or with others). Multiple installations of the same software will show here as multiple lines.

 

A portal from Faculty to Software will show you all software that is available to a person, once.

 

The same applies in the opposite direction. You may need additional TO's of the Computers table hanging off the join tables, so that you can show the correct computer's name in the portals.

Link to comment
Share on other sites

Hi, I am working together with beju0506 and this is the problem.

 

Software(table) licenses are assigned to computers(table) through a join table, and computers (table) are assigned to faculty through a join table.

 

When in the Faculty layout, we can see through a portal all the software that is assigned to this faculty let's say this faculty has 10 licenses of Adobe Photoshop and 5 licenses of Dreamweaver, then in the portal we would see, 10 rows of Adobe Photoshop and 5 rows of Dreamweaver.

 

However, we would like to be able to see trough a portal how many licenses of each Software this faculty has. E.g. 10 licenses of Adobe Photohop, 15 licenses Macromedia Dreamweaver and so.

 

The question is, would there be, a way through autocalculating to create a table or using an existing table to count each instance of the Software assigned to this faculty, essentially grouping it together so in the portal we would only see a Software Name once with the count on how many times this software is on the computers assigned to this faculty.

 

An example for a faculty having 20 computers would be if he or she is responsible for them.

 

 

Thanks for any help.

Link to comment
Share on other sites

Maybe I should just show an example:

 

currently we see:

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Adobe Photoshop 1

Macromedia Dreamweaver 1

Macromedia Dreamweaver 1

Macromedia Dreamweaver 1

Macromedia Dreamweaver 1

Macromedia Dreamweaver 1

 

But we would like to see:

Adobe Photoshop 10

Macromedia Dreamweaver 5

Link to comment
Share on other sites

Yes, this is a tough one. If two people are assigned the same computer, you want to count the same installation TWICE.

 

 

Possible solutions, in increasing order of difficulty:

 

1. Create a individual report for each person: from Faculty, Go to Related Record (show only related) in the Installations table, summarize by Software.

 

2. Almost the same as 1, but the script sets a field in Faculty with the summary information, so it can be viewed when browsing Faculty.

 

Both scripts can be looped to go thru the entire Faculty.

 

3. Use one of the methods described here to get a live calculated summary of related installations in Faculty.

Link to comment
Share on other sites

After some more thought, there's another possibility, that's not too hard to implement, and will provide a live summary. The down-side: it will not refresh as you browse from one person to another, so navigation in the Faculty table must be scripted.

 

The method:

 

In Software, define a global field gPersonID. This field needs to be kept synchronized to the currently viewed Faculty record, hence the need for scripted navigation: every time you change the viewed record in Faculty, you must also set gPersonID in Software to the current PersonID, AND call a Refresh Window step, with the option to flush cached result checked.

 

Define a valuelist "MyInstallations" of InstallationIDs (include only related values, starting from Faculty).

 

In Faculty, define an unstored calculation field cMyInstallations (result is text) =

ValueListItems ( Get (FileName) ; "MyInstallations" )

 

Place another TO of Faculty on the graph, name it CurrentPerson, and define a relationship:

Software::gPersonID = CurrentPerson::PersonID

 

In Software, define an unstored calculation field cCurrentInstallations (result is text) =

CurrentPerson::cMyInstallations

 

Place another TO of Installations on the graph, name it CurrentInstallations, and define a relationship:

Software::SoftwareID = CurrentInstallations::SoftwareID

AND

Software::cCurrentInstallations = CurrentInstallations::InstallationID

 

In Software, define an unstored calculation field cCount (result is number) =

Count ( CurrentInstallations::InstallationID )

 

Add the field cCount to the portal on Faculty layout showing related records from Software.

Link to comment
Share on other sites

Comment, the link to the Dynamic Grades example was perfect! Exactly what we're looking for! We want to be able to have an automatic lookup to put this data (along with faculty name) in another table, so that is our next step, but this was definitely huge for us! Thank you greatly!

Link to comment
Share on other sites

We realized we need the individual counts of the software to be separate records in a separate table. We looked at the fixedgrades example you gave and it was similar to what we need.

Really the end product needs to be a separate table that has individual records for each software name, with faculty id and a counter that lists the instances of the software for the faculty:

for example:

 

UniqueID Faculty SoftwareName Count

1 joe XP 3

2 joe Illustrator 5

3 pete Macos 6

4 joe MacOS 6

 

 

So later on we'll be able to display in a portal on the faculty layout and get all of the related records for that faculty and just show one instance of the software name and the count.

So in the portal, using the records I described above, on the layout for faculty member "joe", we would have a portal that shows:

 

XP 3

Illustrator 5

MacOS 6

 

The key field between this and the faculty table would be faculty name.

Because we will be dynamically adding software titles to our db as time goes on, we can't use a fixed list of names as in the example (which has one field per value).

 

The reason we want to have this is to be able to use the data in other functions, reports, etc. We need to be able to manipulate this data and not just show it in a report or a text field.

Link to comment
Share on other sites

I think it's clear that such table cannot be dynamic - only a snapshot of the current situation. So it has to be created by a script, and updated periodically. On the bright side, using a script should be simpler than any calculation method.

 

So later on we'll be able to display in a portal on the faculty layout and get all of the related records for that faculty and just show one instance of the software name and the count.

 

If that's the only purpose of the table, then you don't need it. The last method I outlined will give you exactly this.

Link to comment
Share on other sites

First of all, thanks for all the good information we have gotten from you.

 

I agree, if we were only to display the data through a porta, then the autocalc function you supplied would be sufficient. But that is not the case.

Nevertheless, there is more informatin associated with Software e.g. when maintenance is due for renewal, consequently, how much it will cost, how many licenses, are still available that were allocated to this particular faculty responsible for a lab, to name of few.

Therefore, having this information in seperate table would make it easier to manipulate the data in any way we want and of course display it that way.

 

Forgive me my ignorance, but my understanding of scripting is very limited, but it seems that a Script always requires an extra step, meaning something that has to activate it, whereas a function does everything without user interaction. That is the reason, why we wondered if there is a way to have a function, or use the existing function, or if there is a way to set up a relationship so when we assign a computer with software on it to a faculty, there is a table, or a field in an existing table, that would automatically keep track of how many software licenses each faculty would be assigned to. We did like the class example with the fixed keys, but since software is added and changed on a constant basis that would not work.

 

Nevertheless, since our understanding of Filemaker pro is limited, if a script is the way we have to do then that is what we have to do it.

In this case, if it would be possible to send us a sample for this method that you mentioned, that would be great.

 

"The method:

 

In Software, define a global field gPersonID. This field needs to be kept synchronized to the currently viewed Faculty record, hence the need for scripted navigation: every time you change the viewed record in Faculty, you must also set gPersonID in Software to the current PersonID, AND call a Refresh Window step, with the option to flush cached result checked."

 

Thanks agian, for any help you can give us.

Link to comment
Share on other sites

I am not sure we are all on the same page yet.

 

Let's take a simple example: There are 2 people, Adam and Betty, each has a computer. There's a third computer, assigned to Adam and Betty together. A fourth computer is assigned to Cecil. All four computers have Windows installed.

 

Now, IIUC, you want to see something like this:

 

Adam

2 Windows

Betty

2 Windows

Cecil

1 Windows

 

The problem here is that in your structure, you are assigning Computers to People, and installing Software on Computers - but you never assigned Software to People. Although there are only 4 installations of Windows, in your report you want to see a total of 5.

 

 

Basically, you have two options:

 

1. Generate (by script) a separate table, where each person's installations are compiled together (one person after another)

 

2. A dynamic calculation

 

The first option creates a static snaphot. That's not necessarily a bad thing: it only takes a few seconds to generate, so you can simply generate it anew any time you want to look at it.

 

The second option, a dynamic calculation, has this limitation: you can only look at one person at a time. That's because the same single Installation (on the computer assigned to Adam and Betty together) has to be counted TWICE: once when looking at Adam, and once when looking at Betty.

 

This means you need to tell the Software table which person you are looking at now. Hence the need for a global field gPersonID in the Software table, and the need to synchronize it with the currently viewed record in the People table.

 

The synchronization can be very simple: hide the status area, and provide buttons to move between records. The buttons call a script that (1) goes to previous/next record, (2) sets the global field gPersonID in Software to the current record's PersonID, and (3) refreshes the screen.

 

Although there's a script involved in this option too, there's a difference. The navigation script does not create/modify any core data - it only sets the viewing conditions.

 

Hopefully this makes it clearer.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use