TableRelation and Secondary key? - help

abartonicek

Can TableRelation property be used with secondary key?

ngebhard

Not as far as I know. You give the relation to a table without any key.

Greetz
Nicole

abartonicek

The reason I'm asking that is that I have two tables - Jobs and UniqueStamp.

Table UniqueStamp has No. field from Jobs table and (among others) StampNo. field.
The Primary key in table UniqueStamp is ONLY field StampNo. (StampNo. must be unique no matter what job number is used)
and I have to have TableRelation to Jobs table by No.

Any suggestions about that TableRelation?

ngebhard

You mean you want to relate to a certain field which is not part of the primary key?!

Try this:
If you're standing in the table relation-property, use F6 to specify the table relation. Enter the table no. in the field table. Then, using F6 in the "field"-field, you can choose the field the relation goes to.

This should actually work!

Regards
Nicole

abartonicek

I know about that. I set the Table Relation like that but it doesn't seem to work. Maybe I'm doing something else wrong.

From Job Card I call UniqueStamp List (editable for now) where I add some Stamps but I get error Job No. "" doesn't exist.

For some reason the Job No. is unknown.

RunFormLink for UniqueStamp List is set to StampNo.=FIELD(No.)

DenSter

The "No." field is the identifier of the Job table. You need to map the StampNo field in the Job table to the ID field in your UniqueStamp table. That should take care of filling the right values and validating the stamp number when you enter one in the Job Card.

<elaborate>
This is how I understand yur setup:

UniqueStamp table:
StampNo field, which is the primary key
[other fields]

Job table:
StampNo field, which needs to be the same data type and length as the StampNo field in the UniqueStamp table, and it has a TableRelation to the StampNo field of the UniqueStamp table

The TableRelation property would look like this:
UniqueStamp.StampNo
Since the StampNo field is the primary key, that's all you need to make this relation complete.

Now if you want to limit the uniquestamp records visible from the Job Card, you need to add a "Job No." field to the UniqueStamp table, with a relation to the "No." field of the Job table. Then, in the Job table, you can add a filter to the tablerelation to the stampno field.
</elaborate>

ngebhard

I suggest that this is the problem:

RunFormLink for UniqueStamp List is set to StampNo.=FIELD(No.)

If you're in the job card, the no. is the job no.

If you wanna see all StampNos that belong to the current project, you need to have JobNo=FIELD(No.). Or did I missunderstand the configuration?

Greetz
Nicole

abartonicek

Ok, this is the situation:

The Job table is unchanged!

Job table:
No.
[other fields]

UniqueStamp table:
StampNo. -> Primary key
No. -> table related to Job No. => Secondary key
[other fields]
Desired funcionality:

For every Job I can have some number of Stamps that are represented by their number (StampNo.)

The key is that Stamps MUST be UNIQUE (not unique for certain Job!)

Maybe it's simple problem but... :?:

ngebhard

But then you don't have a field reference but a menu point to open a new form with the stamps in it, right?

In the menu button, where you run the form "stamps", you run the form using the RunFormLink-property. This must be "Job No=FIELD(No.)" as well, because you wanna see all the entries in the UniqueStamp table, that has the actual job no (the job you're in) in the field JobNo.

Your entry (StampNo=Field(No.)) filters the UniqueStamp table in the field StampNo and expects the JobNo to be in this field. I still suggest that this is the problem!

Hope I got it now.

Regards
Nicole

abartonicek

Actually, RunFormLink is No.=FIELD(No.)
The first No. is from my table and the second No. is No. from Job table.

Yes, you got it right, my post was wrong.
The StampNo=Field(No.) should write No.=FIELD(No.) because field that referes (is table related) to Job No. is called No. not StampNo. Sorry.

When I try to enter new Stamp record from Job Card I can enter StampNo but the record is filterd out from the List becouse Job No. is unknown.
My question is: From Job Card, how do I set No. field in UniqueStamp table to current Job No. automatically?

This all worked well before when I had No. and StampNo as fields in primary key of UniqueStamp table.

Then I had to make StampNo unique and.... you know the rest

ngebhard

If the field is a primary key field you get automatically the filter value in the field while entering a new line. If it's not, I would try to put

"No":=GETFILTER("No"); //This is your job no.

in the "OnNewRecord"-trigger of the form (UniqueStamp-Form). This should work. Maybe there is a better way, but this is the one that comes to my mind first.

Greetz
Nicole

abartonicek

If everything worked fine before when PK was No.,StampNo.,
what should I change now when PK = StampNo. and SK = No.

Everything else is the same.


PK=primary key
SK = Secondary key

ngebhard

Did you try to implement the coding as I posted before? This should work well even if the (job) no. is not in the primary key any more.

Which problems did you have with the coding?

Greetz
Nicole

abartonicek

Sorry, I posted before I saw yours.

It worked! =D> =D> =D>

Thanx a lot!

Could you explaine my what did I gain by setting No":=GETFILTER("No") ?
I get current filter and?

Sorry for being pain in the....

ngebhard

The function GETFILTER("No") gives you the value of the filter in the field "no." as you can see in the table filter. Therefore the code No":=GETFILTER("No") enters this value in the field "no." again. If you put this in the "OnNewRecord"-trigger the field "no." while be pre-defined with the filter, what is exactly what you we're trying to do!

It was a pleasure to help you.
Finally we figured out! =D>

Greetz
Nicole

abartonicek

Nicole,
you save the day