Best way to link two tables with unusual layouts? 2

[tjc240e]

I have a member table with a donation envelope number in it and I have the donation spreadsheet with the envelope number in it. The problem is the donation spreadsheet, has a line with the env number and contact info, then under it are the donations:

1 Dr. Joe Jang
03/28/2004 $50.00 2030
05/02/2004 $0.00
06/13/2004 $0.00
4 Mr. and Mrs. John L. Doe
01/04/2004 $170.00
02/22/2004 $25.00
5 Mr. and Mrs. David Smith
01/04/2004 $50.00
01/11/2004 $50.00
01/18/2004 $50.00
01/25/2004 $25.00 2090

Does anyone have a slick way of being able to match the first line of contact info (env number) and then tell Foxpro to cycle through until it hits the next env number?

Does this make sense?

All help is very much appreciated. Thanks in advance.

Tom Cusick

 

[audiopro]

I would suggest you set up a one to many relationship between your member table and a child table containing the donations. By using SKIP in the parent table you would automatically get each donation in turn.

Keith

http://www.studiosoft.co.uk

 

[tjc240e]

But how would the Member table know how many donation records there were in the child table? Would i have to do something like

Do while member.envno = child.envno
...

But that wouldn't be able to find the ones inbetween because there is no actual env no for those records...

Sorry I'm being brain dead today...

(PS how do i put the code in that code block? my network guy has all external links blocked so i can't see the TGML codes.. if someone could email them to me I would appreciate it)

 

[audiopro]

Code snippets
square brackets enclosing the word 'code' at the start and square brackets enclosing '/code' at the end

In a 1 to many relationship, subsequent SKIP commands skip through matching records of the child table.
You would need all records in the child table to contain an envelope number.
Are there any references at all in the records without env numbers.

Keith

http://www.studiosoft.co.uk

 

[tjc240e]

Nope what you see in that spreadsheet up above is what is in there... and there are 5000 records in that spread sheet... *That is going to be a lot of manual re-entering*

 

[audiopro]

Manual re-entering?
If all the records are grouped together, import them into a table from the spreadsheet and write a little routine to replace the blank fields with the last number until it encounters a new number.
If the info is in date order you have a problem - but if that was the case, how would you know who the donation was from?

Keith

http://www.studiosoft.co.uk

 

[tjc240e]

I think this will label me as the lowest form of FoxPro user there is... can you give me an example of a little routine to do that?

It is not in date order.

 

[darrellblackhawk]

Am I correct in assuming that the spreadsheet data is organized in columns thusly?
[tt]A B C D
1 Dr. Joe Jang
03/28/2004 $50.00 2030
05/02/2004 $0.00
06/13/2004 $0.00
4 Mr. and Mrs. John L. Doe
01/04/2004 $170.00
02/22/2004 $25.00
5 Mr. and Mrs. David Smith
01/04/2004 $50.00
01/11/2004 $50.00
01/18/2004 $50.00
01/25/2004 $25.00 2090

Is so, are the column values such?
Columns:
A Envelope #
B Donor / Date
C Donation Amount (the column is formated as currency?)
D Check number
[/tt]

(I hope the spreadsheet data isn't all in one column - doesn't look like it.)

Have you been able to pull the spreadsheet data into a VFP table? Via Import From type xls or Append From Type xls?

Once you get the data in, it's trivial to insert it into another table.

You'll need to give a little more detail on your structure.

Anyway, here is a little program that takes input from
a spreadsheet formatted as mentioned above, and performs
something similiar to what I believe you want.

* [URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?page=1&qid=951781#postform[/URL]
CLOSE DATA ALL

LOCAL cMyPath

cMyPath = "C:\Documents and Settings\Darrell\My Documents\"

CREATE CURSOR Donors (Donor c(64), DonorId i)

CREATE CURSOR Donations ( ;
    DonorId i, ;
    Envelope N(6,0), ;
    DonationDate d, ;
    Donation N(8,2), ;
    CheckNum c(10) ;
    )

=GetDonorData(cMyPath,"Testdonor.xls")


PROCEDURE GetDonorData
    LPARAM cSrcPath, cXlsName
    * cSrcPath - Path to spreadsheet
    * cXlsName - Name of spreadsheet

    * Perform error checking on parameters here...

    * ....

    LOCAL nWkArea, cTblName
    nWkArea = SELECT() && Save current work area
    cTblName = JUSTSTEM(cXlsName)

    * It's assumed that the fields will be of the following types:
    *	Column Name	Description	Assumed Type
    *	A				Envelope		N
    *	B				Donor / Date	C
    *	C				Donation		N or Y(currency)
    *	D				Check Number	C

    CREATE CURSOR DonorImport ( ;
        Envelope N(6,0), ;
        Donor_Date c(64), ;
        Donation N(8,2), ;
        CheckNum c(10) ;
        )

    APPEND FROM (cSrcPath+cXlsName) TYPE XLS

    LOCAL bEnvChange, nCurntEnv, cCurntDonor
    LOCAL dDonationDate, nDonation, cCheckNum
    LOCAL nDonorId

    nDonorId = 0

    SCAN

        bEnvChange  = DonorImport.Envelope <> 0
        nCurntEnv   = IIF(bEnvChange,DonorImport.Envelope,nCurntEnv)

        IF bEnvChange
            cCurntDonor = XLSCharCleanse(DonorImport.Donor_Date)
            nDonorId = nDonorId + 1
        ELSE
            dDonationDate = CTOD(XLSCharDateCleanse(DonorImport.Donor_Date))
        ENDIF

        nDonation = DonorImport.Donation
        cCheckNum = XLSCharNumberCleanse(DonorImport.CheckNum)


        * Write record to Parent and child table here.
        * Or you might perform some type of lookup...

        IF bEnvChange
            INSERT INTO Donors (Donor, DonorID) VALUES (cCurntDonor, nDonorId)
        ELSE
            INSERT INTO Donations (DonorId, Envelope, DonationDate, Donation, CheckNum) VALUES ;
                (nDonorId, nCurntEnv, dDonationDate, nDonation, cCheckNum)
        ENDIF

    ENDSCAN

    USE IN "DonorImport"
    SELECT (nWkArea)
ENDPROC


* Spread sheets can add some funky chars to character columns
* that won't get stripped by alltrim(), so clean them out using
* something similiar to these functions...

FUNCTION XLSCharDateCleanse
    LPARAM vArg
    vArg = ALLT(vArg)
    LOCAL nLen, i, cNextChar, cReturn
    cReturn = ""
    nLen = LEN(vArg)
    FOR i = 1 TO nLen
        cNextChar = SUBSTR(vArg,i,1)
        IF cNextChar $ "1234567890/"
            cReturn = cReturn + cNextChar
        ENDIF
    NEXT
    RETURN cReturn
ENDFUNC

FUNCTION XLSCharCleanse
    LPARAM vArg
    vArg = ALLT(vArg)
    LOCAL nLen, i, cNextChar, cReturn
    cReturn = ""
    nLen = LEN(vArg)
    FOR i = 1 TO nLen
        cNextChar = SUBSTR(vArg,i,1)
        * Allow punctuation in text - oh well...
        IF BETWEEN(cNextChar,CHR(32),CHR(126))
            cReturn = cReturn + cNextChar
        ENDIF
    NEXT
    RETURN cReturn
ENDFUNC

FUNCTION XLSCharNumberCleanse
    LPARAM vArg
    vArg = ALLT(vArg)
    LOCAL nLen, i, cNextChar, cReturn
    cReturn = ""
    nLen = LEN(vArg)
    FOR i = 1 TO nLen
        cNextChar = SUBSTR(vArg,i,1)
        * Allow only numbers
        IF ISDIGIT(cNextChar)
            cReturn = cReturn + cNextChar
        ENDIF
    NEXT
    RETURN cReturn
ENDFUNC

Darrell

 

[MrFancyteeth]

definately go with Darrels suggestion

2 tables:
1 with just the donors & a unique ID , then a donation amount table which contains the donors ID linked to their amounts,

then you can filter & sql select away to your heart's content where donor.ID=donation.ID

 

[tjc240e]

Actually its like this:
A B C D E
1 Dr. Joe Jang
03/28/2004 $50.00 2030
05/02/2004 $0.00
06/13/2004 $0.00
4 Mr. and Mrs. John L. Doe
01/04/2004 $170.00
02/22/2004 $25.00
5 Mr. and Mrs. David Smith
01/04/2004 $50.00
01/11/2004 $50.00
01/18/2004 $50.00
01/25/2004 $25.00 2090

Columns:
A Envelope #
B Donor
C Date
D Donation Amount (the column is formated as currency?)
E Check number

 

[darrellblackhawk]

Even easier. Good luck!

Darrell

 

[tjc240e]

Well I got the code over into foxpro and now it's giving me an Microsoft Excel File Format is Invalid error box?

Suggestions?

 

[tjc240e]

Thank you thank you thank you... with a mite tweek it works beautifully.

 

[tjc240e]

Ok I've got the program working but now I'm wondering if there is a way to extract the one field (CheckNum) into it's own database, but make it only be unique values.

I was able to just do

CREATE CURSOR Funds (Fund c(64))
...
IF !ISBLANK(cCheckNum)
	INSERT INTO Funds (Fund) VALUES (cCheckNum)
ENDIF

But I want them to only be of unique values (because some of them are duplicated)

Suggestions?

 

[Stella740pl]

SELECT DISTINCT CheckNum AS Fund ;
FROM WhatsTheTablesName ;
WHERE !EMPTY(CheckNum) ;
INTO CURSOR Funds

Or this might be faster:

SELECT CheckNum AS Fund ;
FROM WhatsTheTablesName ;
WHERE !EMPTY(CheckNum) ;
GROUP BY 1 ;
INTO CURSOR Funds

 

[tjc240e]

THANKS A Million Stella... that is great stuff...

I feel like a kid in a candy store...

Thanks again

 

[Stella740pl]

that is great stuff...
It is.
Read the whole Help topic on SELECT-SQL, you will find much more of the great stuff.

 

[tjc240e]

Actually now that brings up another issue. Is there a better way to search for a specific record and pull up the fields associated with that record. For example i have that Funds database now. I merged that into another database called accounts and associated a number to it. What i want to do is search the account table and if i find the fund name return the number. up to this time (and it's been spotty at best) i've been using something like the following code:

SELECT 11
cacct=ALLTRIM(acct_type)
DO while cfund<>cacct
	DISPLAY cfund,acct_type
	IF cfund=ALLTRIM(acct_type)
 	     cfund=alltrim(acct_num)
	ELSE
	     select 11
	     Skip
	     cacct=ALLTRIM(acct_type)
	ENDIF
ENDDO

But this doesn't alway work... anyone have a better quicker way of getting this done?

 

[Stella740pl]

Looking at this, I think you should INDEX your table on cfund and then do SEEK or SEEK(). Or you can use LOCATE - CONTINUE (it's slower than SEEK). Read Help topics and examples on those and see if that's what you need. Come back with questions.

 

[tjc240e]

Actually We are restricted here as to the amount of stuff on the HDs, and to the site we surf, so we have no Help Files on our computers. I've tried using seek in the past but I've not have much success in using it completely. That table is sorted on the cfund column.

If you have an example I would greatly appreciate it.

Thanks in advance