Many fields per table- field limitations

[crossface]

I am not sure if anyone can help me here but here goes.

I have an application that has a table which has many fields. The size of the fields in many cases is quite small.

I know there is a limit of 255 fields per free foxpro table. The fields for this table appear on a form with pageframes. I could have up to 15 page frames for the form.

Each page frame will have the following fields

So my total # of fields could be around 900.

15 checkboxes 1C
15 drop down combos 10C
15 spinners 2N
15 text boxes 250C

Is there any way that I can easily combine these when saving them to a file. They could be variables on the form and be written to a memo field when saving to a file or concantenating the 15 fields together and have one field for each of the 15. Therefore, I could eliminate all the fields and thefore limit the number of tables that would have to be associated with all this data.

I hope I am making sense here. I would have to dynamically combine all the varaiables when saving and break them apart when opening up the form. Is this to much trouble?

Any suggestions would be appreciated.

Kevin

 

[rgbean]

How likely is it that all 900 "fields" will all have data for each "record"? You need to do some reading on table normalization. You could easily put all 225 check boxes into a single field - that saves you 224 fields!

Rick

 

[sermac]

Rick, you wrote:

You could easily put all 225 check boxes into a single field - that saves you 224 fields!

Can you explain how this is possible? Can you do it with other types of fields? I appreciate any feedback. Thanks.

Bill

 

[johncook]

Kevi,
If it were me, and I do not know if this is an option, I would consider breaking "like" data to separate forms, just for easier maintenance by the users. 900 fields to maintain on a single form just sounds totally overwhelming.

As far as combining the data into a memo field then separating it for the display, I guess it depends on our usage of the data throughout the app. Imagine having to parse thru the memo field to get a piece of that data when some part of you application needs it.

Having said all that, I guess you could put each piece of data(field) on a separate line in a single memo field. If you wanted to do that it might be something like:

1. name each object(field) on the form as Fld001,Fld002,Fld003, etc. Then:

lmmemovalue=""

For lnfldcounter = 1 to 900
lmmemovalue=(lmmemovalue+alltrim(EVAL("Fld"+right("000"+STR(lnfldcounter),3)))+CHR(13))
endfor

and write lmmemovalue to the one memo field in the table.

Others may have much better solutions,
John

 

[jimstarr]

Hi Kevin,

My personal opinion is that you'd create quite an ungainly, hard to understand system if you crammed lots of distinct fields into one string.

I'd go about it by creating the "record" as a compendium of single records from 4 different tables. These records would be related on a one-to-one basis, a snap in VFP.

Jim

 

[crossface]

Could you explain what you mean by creating a record as a compendium of sinfle records from 4 different tables?

Thanks

Kevin

 

[jimstarr]

Tables A, B, C and D are related by an ID field. Table A contains fields 1 - 250, table B contains fields 251 - 500, etc.

Jim

 

[crossface]

Gotcha

 

[rgbean]

Bill,
I'm sorry, I was offering that solution with "tonque-in-cheek" - that is I wasn't really suggesting that as a best solution. On the other hand, I've used this technique to store a bunch of configuration flags to make it easier to access them. You can treat this flagfield as a string array, To check a value, use something like:
IF SUBSTR(flagfield, nFlag, 1) = "Y"

And to set it, you can use:
flagfield = STUFF(flagfield, nFlag, 1, "N")

If you wanted to you could use a header file (.H) and include definitions like the following to "name" the positions and make the code more readable.
#DEFINE d_Bold 1
#DEFINE d_Underline 2
#DEFINE d_Italic 3

Then the examples above might read:
IF SUBSTR(flagfield, d_Bold, 1) = "Y" && Check for Bold

flagfield = STUFF(flagfield, d_Italic, 1, "N") && Set Italic Off

Rick

 

[sermac]

Thanks Rick:

Craig used to have the handle "Slighthaze". . . he and you/others are very good!. . .I was going to change my handle to "FullFog". . .after I read your comment.

Have been using what Jim Starr described above:

"Tables A, B, C and D are related by an ID field. Table A contains fields 1 - 250, table B contains fields 251 - 500, etc."

After I read the other comments. . .it made more sense.

Thanks for the feedback.

Bill