Jump to content
Salesforce and other SMB Solutions are coming soon. ×

switching from repeating fields to tables


Ron Steriti
 Share

Recommended Posts

My question involves portals and tables.

I am switching from repeating fields to tables.

Previously I used the AND relationship to match both fields.

It's more complicated now that each of those repeating fields is now a separate table.

I'm able to match the first half of the criteria, but not the second.

 

Here's a summary - I have a database of diseases, dietary supplements and studies.

 

The tables

Diseases

Supplements

Supplement_Disease

Studies: Study

Study_Disease: Study and Disease

Study_Supplement: Study and Supplement

 

The relationship graph looks something like this.

We are looking at records in Supplement_Disease

 

Supplement_Disease Study_Supplement

Supplement ------> Supplement Study_Disease

Study -------------------> Study

Disease --->

 

Supplement_Disease 2 Study_Disease 2

--> Disease --------------------> Disease Studies

Study ----------> Study

 

The idea is to show studies that match both the supplement and disease.

Switching Supplement_Disease 2 to Diseases also doesn't work.

 

Any help would be greatly appreciated!!

(Especially an example)

Link to comment
Share on other sites

Here's the relationship graph again with _ for spaces and = for the relationship.

 

Supp_Disease _ Study_Supp

__ Supp ======= Supplement _ Study_Disease

______________ Study ======== Study

_______________________ ______Disease ===

 

Supp_Disease 2 _____ Study_Disease 2

=== Disease =========== Disease ______ Studies

_________________________ Study ====== Study

Link to comment
Share on other sites

I'm sorry, what's the question?

 

Do you need help creating related records in a new table based on information that's currently bottled up in repeating fields? Or have you already done that and you're asking about something else?

Link to comment
Share on other sites

I'm sorry, what's the question?

Do you need help creating related records in a new table based on information that's currently bottled up in repeating fields? Or have you already done that and you're asking about something else?

 

Actually, I've already created new tables from the repeating fields.

 

Originally I used the AND relationship on repeating fields (Disease X Supplements).

 

The problem I'm having is how to do that with the new tables

(Study_Disease and Study_Supplements).

Link to comment
Share on other sites

I've avoided repeating fields like the plague, detesting the $#!@ things, and therefore would not know an "AND relationship on repeating fields" if it started chomping on my ankle.

 

(I did once inherit a db that had not only relationships but lookups based on repeating fields.... ugh...)

 

Can you explain what it is that you're trying to do and what's causing you trouble? If you need to talk about relationships that involve repeating fields, pretend I'm STOOPID and break it down in excruciating detail, thanks.

Link to comment
Share on other sites

Can you explain what it is that you're trying to do and what's causing you trouble? If you need to talk about relationships that involve repeating fields, pretend I'm STOOPID and break it down in excruciating detail, thanks.

 

OK

 

I've got a three main tables:

Diseases

Supplements

Studies

 

And three tables with combinations:

Supplement_Disease

Studies_Disease

Studies_Supplements

 

I want to show only the studies that match both Supplement and Disease in a portal on a record from the Supplement_Disease table.

 

I thought that this would be the way (ignore the name swap)

 

Disease_Supplement = Supplement_Studies = Studies_Disease = Disease_Supplement = Supplement_Studies

Link to comment
Share on other sites

EDIT 9.24.06 10:42 AM EST:

 

Multikeys!

 

(And now I really do feel STOOPID). I woke up this morning with a sense of how you had been using repeating fields. You were using them in lieu of multikey relationships.

 

A multikey relationship is where you have a text field on at least one side of a relationship, and it contains multiple values separated by a hard return, like this:

 

Value 1

Value 2

 

That field will match a record on the other side of the relationship containing either Value 1 or Value 2. Or, looking at it from the other table's vantage point, a record containing Value 1 would match that record, and so would a different record containing Value 2.

 

That, in combinaton with an AND relationship tying your two fields (Supplements and Diseases) in Supplement_Disease to your two fields (Supplements and Diseases) in Studies, should do the same thing you were doing with repeating fields.

 

[/EDIT]

 

 

OK, let's see if I've got this straight.

 

You've got a table, Studies; it has a field in it that specifies which Supplement each Study record is associated with; and it has another field specifying which Disease the Study record is associated with.

 

I assume that each study may be associated with more than one Supplement and more than one Disease, using multikeys?

 

 

You are on a layout that is native to the Supplement_Disease table, a table that also has either

 

• one field specifying a Supplement and another field specifying a Disease (I did mine that way); or

 

• one field that concatenates the specification of Supplement and Disease into a single value

 

and on this layout you wish to put a portal to Studies, showing only those Studies that pertain to both the Supplement and the Disease pertinent to the record you're currently on?

 

 

Set up a relationship in which Supplement_Disease is related to Studies on the basis of Suppement=Supplement AND Disease=Disease.

 

Here's an example I banged out.

 

There is one record in Supplement_Disease ("Vitamin A / Typhoid"); it has a portal to Studies showing two matching Studies records "Joe's Study" and "Peggy's Study". Joe's Study is concerned with Vitamin A and Thiamine and with Typhoid and the Common Cold. Peggy's Study is concerned with Cod Liver Oil and Vitamin A and with Rabies and Typhoid.

 

Non-matching Studies records include Bob's Study (Vitamin B and Typhoid) and Anna's Study (Vitamin A and Leprosy).

 

If you are restricted from being able to download files, say so along with an email address and I'll just email it to you.

Link to comment
Share on other sites

I've avoided repeating fields like the plague, detesting the $#!@ things, and therefore would not know an "AND relationship on repeating fields" if it started chomping on my ankle.

 

 

If you enter find mode on a layout that contains a portal, you will notice that you can-t move beyond the first row. This prevents you from performing an AND search that would allow you to find a record containing 2 different items in the same portal.

 

You can do a search if you redefine the related field showing in the portal as repeating.

The design is not used to store data in the repetitions, it is just to allow FM to accept more values when entering find mode.

Link to comment
Share on other sites

If you enter find mode on a layout that contains a portal, you will notice that you can-t move beyond the first row. This prevents you from performing an AND search that would allow you to find a record containing 2 different items in the same portal.

 

You can do a search if you redefine the related field showing in the portal as repeating.

The design is not used to store data in the repetitions, it is just to allow FM to accept more values when entering find mode.

Hmm, I'll have to experiment with that.

 

I hardly ever do finds in portals because a Find for a record in Table A with a value of "> 7:00 AM" in RelatedTable::Departure Time and a value of "

 

And the finds, necessarily involving unindexed values, are annoyingly slow.

 

So I usually write a script that accepts find requests in globals (on a replica layout), then performs the find in the related table(s) for the fields that are in that/those table(s) and loops through the found set(s) setting variable(s) to the unique IDs of whatever related Table A values were brought up, then goes back and does the find in Table A for the fields that are native to Table A, then does a final loop tossing out values from the found set that do not match via PatternCount the variable(s) storing "hits" from the loops in the other tables.

 

It sounds horribly cumbersome but it's actually as much as 20 times faster than doing a find on related values, especially with large tables. And you don't get the Departure Time / Arrival Time fiasco.

 

----------------

 

I suppose if one is going to be a prolific opinionated know-it-all, one should distinguish between assertions based on knowledge and assertions based on taste. My dislike for repeating fields is mostly a matter of taste. I get tripped up by their limitations and have had nothing but bad luck when I've tried to use them, but other folks do find them useful. I'll still probably recommend against them but I should try to make it plain that not everyone regards them as bad design.

 

The OP's use of them as a substitute/analogue for multikeys is interesting.

Link to comment
Share on other sites

You don't have a single table that has fields for both the Disease and the Supplement.

 

Aren't those characteristics of the Study?

 

This database looks to still be in its infancy. Most of your tables have one or two fields only. I'd start over.

 

You need a Studies table. You need a Diseases table. You need a Supplements table. Your Studies table should have a field that contains keys for all the Diseases that the Studies record is about, and another field for all the Supplements that the Studies record is about.

Link to comment
Share on other sites

You don't have a single table that has fields for both the Disease and the Supplement.

Aren't those characteristics of the Study?

This database looks to still be in its infancy. Most of your tables have one or two fields only. I'd start over.

You need a Studies table. You need a Diseases table. You need a Supplements table. Your Studies table should have a field that contains keys for all the Diseases that the Studies record is about, and another field for all the Supplements that the Studies record is about.

 

The actual database is quite large, with over 200 diseases, 300 supplements and 500 studies, drugs, lab tests, etc. I made a simple database to show what I was looking for.

 

I did have repeating fields in the Studies table - related_diseases and related supplements. I switched to tables (Study_Disease and Study_Supplement) because portals have advantages over repeating fields, except for this issue.

Link to comment
Share on other sites

OK. (Indeed they do).

 

Well, you have direct relationships between the Studies table and the Study_Disease table, and again between the Studies table and the Study_Supplement table.

 

But Disease_Supplement is floating off in a different world, linked to a different table occurrence of Study_Disease (Study_Disease 2) than the the TO that's related to Studies; likewise to a different TO of Study_Supplement than the TO that's related to Studies.

 

That means there's no relationship between Disease_Supplement and Studies.

 

Can you explain why you created separate TOs and thereby have two entirely separate dataworlds in your system?

Link to comment
Share on other sites

OK. (Indeed they do).

Can you explain why you created separate TOs and thereby have two entirely separate dataworlds in your system?

 

I have a lot tables in my database, which makes the relationship map very complex.

I just started using a lot of TO's to stop the spaghetti and make it easier to understand.

Link to comment
Share on other sites

It looks like, instead of storing the Disease ID or Disease Name somewhere in Studies, you're creating a related record in a table Study_Disease. Similarly, you are not storing a Supplement ID or Supplement Name anywhere in Studies, and are instead creating a related record in a table Study_Supplement. From your rel graph, I gather that you probably have a portal to Study_Disease, and a dropdown list on the "Disease" field in the Study_Disease portal draws from values in the Diseases table; when you select one, a related record is generated in Study_Disease. Similar procedure for selecting a Supplement in the Supplement portal.

 

 

Not a bad or an unusual design, except that for your purposes (wanting a single portal to Studies that only match on both values) it puts you in a bind: you don't have a single table anywhere in sight that contains field information for study, supplement, and disease all in the same place. Hence, no available portal to just the studies that match on both fields.

 

If you change your method of selecting diseases and supplements for each study —*by selecting them from a value list and adding their ID codes to multikey fields in the Studies table itself — you can do what you want easily enough, via a rel like this:

 

 

DiseaseStudiesRels.jpg

Link to comment
Share on other sites

If you change your method of selecting diseases and supplements for each study —*by selecting them from a value list and adding their ID codes to multikey fields in the Studies table itself — you can do what you want easily enough, via a rel like this:

DiseaseStudiesRels.jpg

 

Are you suggesting that I switch back to having repeating fields in Study (Diseases and Supplements)?

 

That's what I had originally and it does work, but I'd wanted to switch to related tables so that I could use portals, which are nice when you have a big study that involves up to 20 diseases and 20 supplements.

Link to comment
Share on other sites

No. God no, don't go back to repeating fields, you're dead right about them.

 

Did you read what I said upthread about multikeys?

 

Were you able to download and examine the sample file I posted for you?

Link to comment
Share on other sites

No. God no, don't go back to repeating fields, you're dead right about them.

 

Did you read what I said upthread about multikeys?

 

Were you able to download and examine the sample file I posted for you?

 

I guess I'll have to sign up for a membership to download files and figure out what multikeys are!

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use