Linking record to anothe table or lookup

[coop9653]

This concept is eluding me, maybe someone can educate me

I have a database of events.
Now I wish to put the events into categories.
I want the categories from a set list.

I did it this way to start
I made a category table, and made a list of categories
In my events table, I made a field for category

then in field rules, I set a lookup to the category table!

This works well.
I can pick the categories and they are populated into the category field of the events table.


BUT its not exactly what I want... why?

Because if I have a category in the category table that is say "frogs"
I decide it should say "hopping frogs"
I can make that change

Now in my events table, all the previous categories are still "frogs"
This is because, I just used the second table as a source to populate the events table!

What I would like! Is to point to the category!
So If I change it, it changes it for all records!

Problem is, stupid me cannot figure out how to do it!
I am kicking myself, because I am guess this is something very basic and just eluding my mind! Maybe more caffeine will help>>>

[DaveM]

Re: Linking record to anothe table or lookup

changing the category in one table will not change the category in all the other tablesthat were originally filled in.

You can use operations and field update to change enmass.

You have to choose the table and then the field, filter on the ones with named frogs, then update to jumping frogs. If you are only doing a couple tables one time, this should be sufficient.

More elaborate ways would be in sbasic and very involved.

[coop9653]

Re: Linking record to anothe table or lookup

thats not exactly what I want to do.

what I really want is a way to point from one table to another.
I have attached a little picture to illustrate

The idea being. you assign categories to the main table
but they can be changed or edited if needed.

I am pretty sure this can be done
but dont knwo how.

I know how to do it in excel!
for all thats worth! HA

[MoGrace]

Re: Linking record to anothe table or lookup

You could try previous values for your lookup. And if you find you want to change one, you can do a quick filter to display the wrong values and change each one. Which is only good if there are a few!

Or... Your categories table which is static could have an ID field and a DESC field. Then in the Events table link to the ID and display the DESC as a link or as a calc using the lookupc() function. Now all you need do is change the static DESC in categories and keep the ID to see the change.

[coop9653]

Re: Linking record to anothe table or lookup

yes, but you still cannot change them later!
if you want the category for frog to change to frogger, you would have to find each record and change them

easy for me to do

totally impossible for my users

[Stan Mathews]

Re: Linking record to anothe table or lookup

yes, but you still cannot change them later!
if you want the category for frog to change to frogger, you would have to find each record and change them

easy for me to do

totally impossible for my users

Have your lookup fill two fields, the category and a category id (only one need to be visible). Create a post operation to post the category to the other table based on same category id's. Give the users access to a button which runs the post operation.

If they decide to change a category they can post the change to all records.

The lookup is intended to aid in data entry.

[MoGrace]

Re: Linking record to anothe table or lookup

Why can't you give the users the option to change the type descriptions with a little pop up form?

Add a new field to Events and Category called Cat_ID.

Update the Category->Cat_ID field to populate it with a series.

Now use a post operation to populate the new field for the ID field you added to Events by matching it to the existing Category field.

Make the Category->Cat ID field rule auto increment and set the default value to the next value in the series.

Change the field rule in Events to lookup Category based on ID but don't display it in the lookup and fill the ID field.

Then in the future, offer the pop up form for the user to edit the Categories without showing them the ID field. You can even let them add new ones.

I did this for a form that has 20 checkboxes to fill in for which 15 have been defined and the other 5 left blank for "futures". The checkbox list is static and I display it in an embedded browse on the form with the blanks initially hidden and where the filled in values already set cannot be changed. There is a lot you can do with this idea. If any of the blanks are filled in they automatically appear in the other form which references their description using lookupc().

[DaveM]

Re: Linking record to anothe table or lookup

ok,
lets try this

in your categories, you need two fields. catid and category.

in your main table, you only need the catid field. Connect category to your main table by the id field in a set. you can still do a lookup on the category, but the field filled will be catid only. That way you have access to your category and any changes in the category field will be instantaineously there for all reords with that catid. You can then use in forms/letters/reports/etc.

Just rember to make the field show "category" view only to keep someone from typing into it on a form or browse. They would change that definition for all records prior, present and future.

[DaveM]

Re: Linking record to anothe table or lookup

sorry Robin,

I didn't see your post.

[coop9653]

Re: Linking record to anothe table or lookup

that very well may work!!

But what about making a set?
I finally figured out how to do that
but I cannot seem to make changes to my categories from the set

sets seem to be a bit confusing.

But should they not do the same thing?

[MoGrace]

Re: Linking record to anothe table or lookup

sorry Robin,

I didn't see your post.

Great minds think alike...

[DaveM]

Re: Linking record to anothe table or lookup

main table to start
connect the categories table to the main set on a one to one by catid in both tables

If you have forms in the main table, you can "copy to" the set which would let you better use the category table.

kinda why we use sets.

[coop9653]

Re: Linking record to anothe table or lookup

that my be where I am making my mistake with the set
I am linking them on the category field

I need to make a seperate linking field that does nothign but link!

Now I have 2 cool ways to tackle this

thanks

[DaveM]

Re: Linking record to anothe table or lookup

I am sure either will work very well. It may even be possible to combine a bit.

[coop9653]

Re: Linking record to anothe table or lookup

You all our excellent help

So far I have the thing working marvelously
using the lookup method
I have not managed to get sets to work right!

anyway, I now have the strangest behavior
If you open the task form
and then look at the category column
you can pick categories

NOW try this, make new record
enter anything
close and save it

now find that just created category
and select it

it brings up the new category form again

WHY?
who OH why?


ps.... if anyone can make the set in the tables go away let me know how
I cannot delete it!