Newbie Layout and Design question

[zarli]

Hello all,

This is my first post here.

I currently have a FMPro7 Database. I originally designed it to give me an inventory of the computer hardware in our office (PCs, Monitors, Printers, etal.). I need to add a new functionality to it.

I now need to track software Licenses; what we currently have and how much more we need to buy. The way i would like it to work is that i can query for a specific PC, then be able to note what software it currently has loaded and what software the user has requested (so that i can budget for Licenses).

For Example, i currently have the only FMPRo License in the office, but we have some users who will need it. I'd like to record my PC as having FMPro and note all the PCs of users that i need to buy Licenses for. This way i know how many Licenses to buy and, after i get the elicences, i remember which PCs to install them on.

I already know how to make the querry for a specific PC. I have tried adding a field called FMPro with two Check Boxes "Have" and "Need." I'd like to be able to have a running summary of the total of "haves" and the total "needs." I'm not sure what the best approach to doing this would be. Perhaps, my idea for the field layout is all wrong.

Please Advise. Thanks

[rockman]

Your current structure will work and a summary of the totals can be obtained by performing a Find for "haves" or "needs" in the FMPro field.

However, if your eventual use of the database is going to include an indefinite number of software titles tracked in the future, your current structure will bloat rather rapidly. Instead consider a table structure like:

tblComputers <====> tblInstalledSoftware <====> tblSoftware

and

tblComputers <====> tblSoftwareNeeds <====> tblSoftware

This is certainly more complicated than what you have now, but it offers infinite flexibility. (It also may be much more than you need).

HTH,

Jeff

[zarli]

Yes, this is just a limited example. There will definately be more titles being tracked.

So you are suggesting that i make 3 more tables.

1) software installed

2) software needed

How would you recomend the fields for these tables be set?

3) total of all software?

If i understand your theory, i need to relate the tables and use a portal to enter the information? The tblSoftware will basically be a set of global fields, that just calculates the totals in each table (i am guessing).

thank you,

Zarli

[Ender]

Welcome zarli!

It's not entirely clear what tables you currently have set up, so below is an ER Diagram showing a good possible structure for what you need. Each entity is a table, the lines represent the relationships. This does not transfer directly into the Table Occurence graph that FM7 uses, but it shows the basic idea. (Underlined words are keys, a chicken foot with a line implies additional key(s) linking the related table.)

To make this structure work properly, you would remove records from the License-Request table and add them to the License-Assignment table as you fill requests.

If you have a structure like this, it is then pretty straight-forward to show Total Assigned and Total Requested for each SW Package. A simple Count(License-Assignment::Equipment ID), and Count(License-Request::Equipment ID) in the SW Package table would do the trick.

[Ender]

>>How would you recomend the fields for these tables be set?

You would probably have data entry layouts for Equipment. On this you could have portals for Licenses Assigned and Licenses Requested. Using conditional value lists to enter available licenses and available SW Packages, respectively, might be the way to go.

To enter new Licenses, I would think a portal through SW Package would be easiest.

[rockman]

My proposed table structure:

tblComputers

--fldComputerID

--fldComputerName

--etc...

tblSoftware

--fldSoftwareID

--fldSoftwareName

tblInstalledSoftware

--fldComputerID

--fldSoftwareID

tblSoftwareNeeds

--fldComputerID

--fldSoftwareID

Yes, and use portals

[dbruggmann]

Hello zarli,

if you only want to track the total number of licenses and don't have to hassle with software versions and/or license packages, you can do it with only two additional tables: Software and SoftwareAssignement. The difference between the proposed SoftwareNeeds and the SoftwareInstalled table is minor, they hold more or less the same data (just the software status differs).

I attach a sample with a portal view and a report (sorry, I had to build it in FM6, but it should be easy to translate for FM7).

PC-SW.zip

[zarli]

Thank you for all the help everyone. I started working on somethign based on Jeff's suggestion, because it was first and seemed simpler. My end result is this:

Tablkes (equipment, softwareInstalled, softwareNeeded, and software)

Relations:

equipment <==> softwareInstalled <==> software

equipment <==> softwareNeeded <==> software

I made a new layout based on the equpiment table with portals to the software I and N tables. they both allow addition and deletion of records based on the portal. the title field is a drop down list which is restricted to the values of the software table. that settles the entry part.

for the summary. i made the software table have a "have" and "need" field both which are calcualtions of the count of the coresponding related title.

It seems to work like a charm. i need to finish soem of th edata entry and make a new layout so that i can do a manual audit. i just want to make sure my numbers are correct, befor i tell my boss we need to order a few $1000 worth of software to upgrade older machines... heh

thanks all,

Zarli