Need total of fields, but number of fields vary

[buddyrich2]

This one may be a little hard to explain but please bear with me.

For each account, I have a table that contains the fields (for example) tot1, tot2, tot3, etc. I also have a field called grand_tot, which is the sum of all these other fields. To get grand_tot, I put "tot1+tot2tot3...." in my program. So far so good.

Problem is: the number of these "tot fields" vary from account to account. If I try to satisfy the largest account, the program of course complains that the field is not there.

Is there anyway I can write a 'variable routine' in the program to take the number of these fields (I could put this number in a master table) and total these columns regardless of how many their are?

Thanks in advance for your help!

 

[Mike Lewis]

Hi BuddyRich2,

The first thing that strikes me is that you have should have designed your table structure to better handle this situation. Instead of having a variable number of fields in different tables, it would have been better if you did a more normalised design, using a one-to-many structure.

That way, you would have a master account table, and also a child table that contains the "tot" values along with a link back to the master. Each record in the child table would contain a single "tot", giving you as many or as few tots as you need for any given account.

However, let's assume it's too late for you to change to that sort of structure. Probably the best way to handle your existing situation is something like this:

lnCount = <number of tots for the current account>
lcCommand = ""
FOR lnI = 1 TO lnCount
  lcCommand = lcCommand + "TOT" + TRANSFORM(lnI) + "+"
ENDFOR
IF NOT EMPTY(lcCommand)
  * Remove final "+"
  lcCommand = LEFT(lcCommand, LEN(lcCommand)-1)

  REPLACE Grand_Tot WITH &lcCommand
ENDIF

This is just off the top of my head, and will probably need refnining, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

Or you could test for their existence:

lcCommand = ""
aBigNumber = 100
FOR lnI = 1 TO aBigNumber
  IF Type("Tot"+TRANSFORM(lnI)) = "N"
    IF !EMPTY(lcCommand)
      lcCommand = lcCommand + "+"
    ENDIF
    lcCommand = lcCommand + "TOT" + TRANSFORM(lnI)
  ENDIF
ENDFOR
IF NOT EMPTY(lcCommand)  
  REPLACE Grand_Tot WITH &lcCommand
ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[JRB-Bldr]

Despite the fact that I question your data table architecture of having multiple tables for each separate account (I mentioned that before in another of your postings), the way to get the number of these fields is to use the AFIELDS() function.

DIMENSION aryFlds(1)
SELECT Acct1DBF
nFieldCnt = AFIELDS(aryFlds)

SELECT AcctInfoDBF
SET ORDER TO AcctNo
SEEK nThisAcctNo
REPLACE AcctInfoDBF.FieldCount WITH nFieldCnt

* --- Then Elsewhere ---
SELECT AcctInfoDBF
SET ORDER TO AcctNo
SEEK nThisAcctNo
nFieldCnt = AcctInfoDBF.FieldCount

FOR Cntr = 1 TO nFieldCnt
   <Do Whatever>
ENDFOR

However another way without having to save the field count per account.

DIMENSION aryFlds(1)
SELECT Acct1DBF
nFieldCnt = AFIELDS(aryFlds)

* --- Calculate Sum Of Numeric Field Values ---
nTotal = 0
FOR Cntr = 1 TO nFieldCnt
   cFieldName = aryFlds(Cntr,1)
   cFieldType = aryFlds(Cntr,2)
   IF cFieldType $ "N,I"
      cFieldValue = EVAL(cFieldName)
      nTotal = nTotal + cFieldValue
   ENDIF
ENDFOR

Good Luck,
JRB-Bldr

 

[buddyrich2]

I guess I should have said that I 'inherited' these tables and this structure from an existing, much larger project - or indeed I would have designed it better from the start.

These are great ideas - let me try them.

I knew you guys would know the answer, as usual! Thanks again.

 

[GriffMG]

Actually that makes for a nice refinement to my sample code:

lcCommand = ""
FOR lnI = 1 TO FCount()
  IF Type("Tot"+TRANSFORM(lnI)) = "N"    
    lcCommand = lcCommand + IIF(!EMPTY(lcCommand ),"+","")+"TOT" + TRANSFORM(lnI)
  ENDIF
ENDFOR
IF NOT EMPTY(lcCommand)
    REPLACE Grand_Tot WITH &lcCommand
ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[JRB-Bldr]

If the only numeric fields in question all begin with "TOT" then I would modify my example as follows:

* --- Calculate Sum Of Numeric Field "TOT" Values ---
nTotal = 0
FOR Cntr = 1 TO nFieldCnt
   cFieldName = aryFlds(Cntr,1)
   cFieldType = aryFlds(Cntr,2)
   IF cFieldType $ "N,I"[B];
         AND LEFT(cFieldName,3) = "TOT"[/B]
      cFieldValue = EVAL(cFieldName)
      nTotal = nTotal + cFieldValue
   ENDIF
ENDFOR

Good Luck,
JRB-Bldr

 

[GriffMG]

Good point!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.