A little help with some coldfusion theroy and code

[NCX001]

Hey guys, I'm kinda new to this whole programming thing so bare with me please. I have a little problem that I just can't seem to get my hear around. I have a working on an inventory management page. It keeps track of what software is installed where and how many copies we have, and serials and such. Well, this page was set up long before I started here and it wasn't too friendly. So I'm editing it and adding some new features. Well one of them is displaying the “available installs count” with the search results.

Here is my problem. The way the system keeps track of what software is installed where is by adding taking the appId from the table it with all the app's information on it (the sn, name, max installs, etc) and creating a new entry in another table that indexs that appId and the workstation it is installed on. (along with an Install id) well the only way to check to see how many copies is installed is to get the record count for a particular appId.

This is where I run into trouble. When a user searches for apps the results page displays the apps that still have installs available. So where the record count of getInstallCount for whatever appId is less than the max installs. Well what is the best way to do this since to get the record count of getInstallCount I need an appID and the appID isn't found until I do the search query. What I'm using now is this but it always returns 0 for current Install Count. See the attached Snippet of code:

<cfif client.availableSoftware eq "true">
      <cfif isDefined('form.searchCriteria')>
            <!--- maybe will get current Install Count --->
      <cfquery name="getAppsID" dataSource="#mainDS#">
          Select id from workstationApps
      </cfquery>
      <!--- loop to find the install Count for each record --->
      <cfloop query="getAppsID">
            <cfquery name="getInstallCount" dataSource="#mainDS#">
              select count(*) as installCount from workstationAppIndex
              where appID = #getAppsID.Id#
          </cfquery>


          <!--- if searched and show only available --->
          <cfquery name="getApps" datasource="#mainDS#">
              select * from workstationApps
              Where maxConcurrentInstalls > #getInstallCount.InstallCount#
              and
              #form.searchType# like '%#form.searchCriteria#%'
          </cfquery>
  </cfloop>
<!--- it doesn't get the current intallCount : ( --->
      <cfelse>
          <!--- if not searched and only available --->
          <cfquery name="getApps" datasource="#mainDS#">
              select * from workstationApps
              Where maxConcurrentInstalls > #getInstallCount.InstallCount#
          </cfquery>
      </cfif>
  </cfif>

I see what it is doing. It is getting the isntallCount for all appIds, but I dont' know how to narrow it down without doing the search first, and I don't know how to do the search with out getting the install count first. Any ideas or advice would rock guys. Like I said I'm kinda new to this programming stuff but it is awesome! Thanks!

Kevin

[taramichael]

Hello,

Thanks for your clarification about coldfusion theory and code. It is useful for all the members of this forum.

Thanks again!