two keys in one field

jim · April 17, 2006, 8:42pm

I want to be able to put two keys in one field. In FileMaker, one would just separate the values by a carriage return, and it would be a perfectly happy key field. What works like that in Servoy?

Thanks,

Jim

bcusick · April 17, 2006, 9:49pm

Nothing.

jim · April 18, 2006, 1:57am

Ok, thanks, Bob. Tried calculating an array (since it seemed vaguely similar to a stack of IDs), but that wasn’t happening. I’ll try something else.

FWIW, I’m trying to create an association between contacts that is totally two-way; that is, each one shows up in the others’ tabpanel. Without making two records in the join table.

bcusick · April 18, 2006, 4:22am

Jim,

If you can describe exactly what you’re trying to do - perhaps I can point you in the right direction…

Happy to help if I can.

jim · April 18, 2006, 2:25pm

Thanks, Bob. I’ll try to be exact…

table: contacts, has pk_contact + lotsa the usual fields
table: affiliations, has pk_affil, fk_con1 and fk_con2, and … ?
form: contacts, has tabpanel: affiliation list

Contact record shows affiliator; affiliatee will be selected (or created) and added to list of affiliations in tabpanel. When an affiliation is made, the affiliation will show in the tabpanel in both contacts’ records.

I tried the following (after selection of affiliatee):

[globals.contact_id has been set with affiliatee pk_contact]

var idAffiliator = [grabs pk_contact from afilliator]
var contactIndex = [grabs index from contact table, affiliator record]
var affList = [form name of affiliation list]
var conForm = [form name of contact form]

forms[affList].controller.newRecord();
forms[affList].fk_con1 = idAffiliator;
forms[affList].fk_con2 = globals.contact_id;
forms[affList].controller.newRecord();
forms[affList].fk_con1 = globals.contact_id;
forms[affList].fk_con2 = idAffiliator;
forms[conForm].controller.recordIndex = contactIndex;
forms[conForm].controller.show()

Works okay, except that the tabpanel will show both newly created records in the list, whereas it should only show one. That is, the affiliator’s record shows up in the tabpanel list of affiliatees… until one clicks outside of any fields on the contact form; then it goes away. Tried (in both tables) controller.saveData, application.updateUI, elements[whatever].requestFocus; still, that pesky affiliator record shows up in the list at first.

So, there’s that problem. Plus, I generally don’t like the idea of creating two join records if one would do. Deletes would be simpler with just one join record, and having two records for what I think of as one function offends my sensibilities, generally. Seems kludgy.

Naturally, I fall back on how I’d do it in FileMaker (for better or worse) and think: ‘fk_con1 & “” & fk_con2’ in another field.* So, I think: how cool if fk_con1 and fk_con2 could somehow be stored in a single field in Servoy, as if an array. Then, filter out the fk_con1 to fk_con1 in the tabpanel relationship.

Jim

*[If the symbol doesn’t show up right in this email, it’s supposed to be a carriage return.]

flusheddata · April 19, 2006, 7:16am

Hi Jim,
You might read this post:
http://forum.servoy.com/viewtopic.php?p=30163&

jim · April 19, 2006, 8:13pm

Thanks, Miguel. Stacking keys with javascript syntax in the calc still doesn’t make for a workable relationship, as far as I can tell. But Bob’s reply to your post got me thinking of another approach:

Fields, fk_con1_temp and fk_con2_temp, are created. A scripted find (onRecordSelection) looks for instances of the pk_contact value in both fk_con1 and fk_con2, then loops through the found set, setting fk_con1_temp to pk_contact and fk_con2_temp to whichever value (between fk_con1 and fk_con2) is not the same as pk_contact.

In describing this to another developer, he noted that it sounds a lot like a FileMaker workaround. A useful skill set afterall, perhaps…

Jim

bcusick · April 19, 2006, 10:05pm

Just make a “join table” with two rows. Easy

jim · April 20, 2006, 12:59pm

As I indicated in an earlier entry on this thread, bob, that was the first thing that I tried and, while it was “easy” to set it up, there were problems with it that had me looking for another approach.

Jim

bcusick · April 20, 2006, 4:16pm

Jim,

I don’t see what problem it creates. If you want to delete that from one person, delete one row in the join table.

If you want to delete that item (regardless of where it shows up) - just find for that ID in the join table and delete those records.

Easy.

jim · April 20, 2006, 6:17pm

What you are describing is indeed easy.

It is not what I am looking for, however.

Perhaps I did not explain with adequate clarity.

Thanks, anyway.

Harry_Catharell · April 20, 2006, 6:42pm

Hi Jim,

jim:
Thanks, Bob. I’ll try to be exact…

table: contacts, has pk_contact + lotsa the usual fields
table: affiliations, has pk_affil, fk_con1 and fk_con2, and … ?
form: contacts, has tabpanel: affiliation list

Contact record shows affiliator; affiliatee will be selected (or created) and added to list of affiliations in tabpanel. When an affiliation is made, the affiliation will show in the tabpanel in both contacts’ records.

I tried the following (after selection of affiliatee):

[globals.contact_id has been set with affiliatee pk_contact]

var idAffiliator = [grabs pk_contact from afilliator]
var contactIndex = [grabs index from contact table, affiliator record]
var affList = [form name of affiliation list]
var conForm = [form name of contact form]

forms[affList].controller.newRecord();
forms[affList].fk_con1 = idAffiliator;
forms[affList].fk_con2 = globals.contact_id;
forms[affList].controller.newRecord();
forms[affList].fk_con1 = globals.contact_id;
forms[affList].fk_con2 = idAffiliator;
forms[conForm].controller.recordIndex = contactIndex;
forms[conForm].controller.show()

Works okay, except that the tabpanel will show both newly created records in the list, whereas it should only show one. That is, the affiliator’s record shows up in the tabpanel list of affiliatees… until one clicks outside of any fields on the contact form; then it goes away. Tried (in both tables) controller.saveData, application.updateUI, elements[whatever].requestFocus; still, that pesky affiliator record shows up in the list at first.

So, there’s that problem. Plus, I generally don’t like the idea of creating two join records if one would do. Deletes would be simpler with just one join record, and having two records for what I think of as one function offends my sensibilities, generally. Seems kludgy.

Naturally, I fall back on how I’d do it in FileMaker (for better or worse) and think: ‘fk_con1 & “” & fk_con2’ in another field.* So, I think: how cool if fk_con1 and fk_con2 could somehow be stored in a single field in Servoy, as if an array. Then, filter out the fk_con1 to fk_con1 in the tabpanel relationship.

Jim

*[If the symbol doesn’t show up right in this email, it’s supposed to be a carriage return.]

Any chance of putting a simple solution together demonstrating this issue.

Perhaps if we can solve the display anomaly then it would be an adequate solution to your problem ?

Cheers
Harry

jim · April 20, 2006, 10:20pm

Thank you, Harry, for your kind offer. Yes, solving the display anomaly would do.

A demo of the unrefreshing issue is attached.

Jim

ps used the example_data server (not sure if that matters to you)

unrefreshing_demo.servoy (5.65 KB)

Harry_Catharell · April 21, 2006, 3:18pm

Hi Jim,

I looked at the demo and you seem to have entered the saveData() command without the closing brackets which meant that it was not actually being done

var idAffiliator = forms.contacts.pk_contact;
var idAffiliatee = globals.g_affiliatee_id

forms.affiliations.controller.newRecord();
forms.affiliations.fk_con1 = idAffiliator;
forms.affiliations.fk_con2 = idAffiliatee;
forms.affiliations.controller.newRecord();
forms.affiliations.fk_con1 = idAffiliatee;
forms.affiliations.fk_con2 = idAffiliator;

//none of the following helps

forms.contacts.controller.saveData();

Change your method to the above and it should be fine

Also, you do not need the button to run in the tab - it can be placed on and run from the contacts form

Hope this helps

Cheers
Harry

jim · April 21, 2006, 5:34pm

duhOops! :oops:

Thank you, Harry!

david · April 22, 2006, 4:37am

jim:
I want to be able to put two keys in one field. In FileMaker, one would just separate the values by a carriage return, and it would be a perfectly happy key field. What works like that in Servoy?

This is doable in Servoy and even useful on occasion. It kind of qualifies as a circus trick though! The limitations are that the field of collected keys can only be used on the right side of a relationship and it can’t be a global field. The following technique is analogous to the SQL statement:

select * from table_name where primary_key IN ('value1', 'value2' , ...)

Note that Servoy doesn’t allow the operator “IN” in a relationship. It does however have the “LIKE” operator and we’ll use this to fake Servoy into returning a list of records equivalent to the SQL statement above. The trick to making this work is to modify the operand on the left side with a calculation (step #2).

The following steps will show in a portal or a tab panel a list of all the records with values in the collected keys (key_list) field:

The field of keys should be a text field, with manually entered or calculated values separated by carriage returns. Let’s assume that records with primary keys equal of 1, 2 and 4 (let’s call the primary key field “ID”) EACH have the following data in the key_list field:

1
2
4

Next create a stored calculation called “ID_calc”:

return "%" + ID + "\n%"

If ID field = 1, then ID_calc = %1 % (the space before the last % character is the carriage return which is denoted by “\n” in JavaScript).

What we have created here is a calculation that we will use to “pattern match” in a relationship. The % character represents any one or more characters.

Now we can set up the following relationship (use the same table):

ID_calc LIKE key_list

This relationship matches the “pattern” in the calculation field on the left side to the list of keys on the right side and returns a found set exactly as if you were to do a search with the pattern in the key_list field.

To see it in action, create a portal based on the relationship and you will get the three records denoted in the key_list field.

Kind of cool for what it is. No extra related table and records needed. The real work with this technique is maintaining the key_list field in all the records.

IT2Be · April 22, 2006, 6:19am

The first thing that comes to my mind is the question WHY you would want to do this?
I know a lot of you people have a FM background. My FM background is more or less that of a virgin so the reason why one would want this is a mystery to me. Can you please explain Jim?

jim · April 22, 2006, 6:57pm

Sure, Marcel. Contacts have other contacts that are linked to them, and show up in a tabpanel list. If I link ‘Marcel’ to ‘David’, then ‘Marcel’ will show up in the tabpanel list on the record for ‘David’, and ‘David’ will also show up in the tabpanel list on the record for ‘Marcel’.

I thought it would be preferable to have one record to join these two contacts, and in FileMaker, a match field may contain as many keys as you want, separated by carriage returns. I thought there might be something similar in Servoy, but there is not.

Jim (who is all set with this now, thanks)

IT2Be · April 22, 2006, 7:00pm

OK, thanks for sharing Jim. And… I would indeed use a join for that but you already guessed I guess

Glad you are all set now with it!

coulombre · April 23, 2006, 6:39pm

Hmmmm… I really have to jump in here…

In FileMaker we tended to use the multi-key relationship because there was not other way. Mostly we used it for filtered portals and such… really as a way to conrol the display of related records.

I would really never want to use this technique for real data. Either in FileMaker or Servoy. EVER!!!

In this case it really is just a poor replacement for a join table with separate records. IMHO it represents a crappy resolution to a many-to-many relationship.

Jim… just say NO!

:-)

Rich

From a “Few Good Men”

Rich: You want answers?
Jim: I think I’m entitled to them.
Rich: You want answers?
Jim: I want the truth!
Rich: You can’t handle the truth! Son…

Topic		Replies	Views
multiple relations...in 1 relation (FMPro style) Classic Servoy	19	12099	January 21, 2005
Filemaker to servoy: multiple keys in portal Classic Servoy	4	4456	July 23, 2004
Multi-line keys - how to ?? Classic Servoy	10	12904	March 9, 2004
Newbie struggling with Servoy Classic Servoy	5	4973	November 10, 2003
strange relationship behavior Classic Servoy	3	1817	May 17, 2006

two keys in one field

Related topics