List of related values

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

List of related values

Postby LOGIsoft » Fri Dec 29, 2006 12:19 am

Hello everyone,

I'm trying to accomplish something similar to the List function (available in a 4GL I will not name :roll: ) in Servoy, which would fetch a list of related values in another table - I would then need to separate each value with a comma, similar to this:

ClientA Items 3, 14, 67, 82
ClientB Items 4, 9, 12, 44, 67
etc.

I've got my calc field all set up, but I just get the first related value for each record. I'm assuming I must use an array here, and I've tried using the getValueListItems function without success (it does say in the manuals that it's not recommended for related values, I know...). Can someone point me in the right direction here please, calc-wise?

Also, is there a quick and dirty way that I could strip off the leading zeros in my comma-separated list? They would actually look like this if I just went and got the values:

ClientA Items 003, 014, 067, 082
ClientB Items 004, 009, 012, 044, 067
etc.

TIA,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Postby IT2Be » Fri Dec 29, 2006 9:08 am

I've got my calc field all set up, but I just get the first related value for each record. I'm assuming I must use an array here, and I've tried using the getValueListItems function without success (it does say in the manuals that it's not recommended for related values, I know...). Can someone point me in the right direction here please, calc-wise?
It would help if you add your code so we can give pointers based on that.
Also, is there a quick and dirty way that I could strip off the leading zeros in my comma-separated list? They would actually look like this if I just went and got the values
You can use the javascript function replace or replaceall. You can also use a regular expression. You can look up the first in the methodeditor and the second is dealt with in many places on the web.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby david » Fri Dec 29, 2006 3:31 pm

This function will return an array of values from of a column you specify through a relationship you specify:

Code: Select all
var array = databaseManager.getFoundSetDataProviderAsArray([name of relationship],'[name of column]')


I would not recommend doing something like this in a calculation though as it will create quite a bit of overhead. Place in a dataprovider via a method triggered on a specific event just when you need it. If you need this calculation to base a relationship on, I would find another way to produce the same results that you are looking for in that relationship.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Postby LOGIsoft » Fri Dec 29, 2006 3:43 pm

Thanks for the quick reply, Marcel.

It would help if you add your code so we can give pointers based on that.


There are three tables involved:

- Speakers
- Talks
- TalksAvailable - a join table between the first two

When a speaker is available to give a talk, both IDs are input in the join table, and a lookup is made there for the talk number from the Talks table (this seems necessary to obtain the talk numbers from only one table away.)

I have a valuelist (avail_talks_VL) that gives me the list of available talk numbers from the lookup field based on a relationship from Speakers to TalksAvailable. I created an additional calc field of text type in Speakers (avail_talks) with this code:

Code: Select all
return application.getValueListItems('avail_talks_VL') ;


which gives me
BufferedDataSet{}
in the calc field avail_talks in my layout based on the Speakers table.

Hope this clears things out. Your help is much appreciated. :D

Sincerely,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Postby LOGIsoft » Fri Dec 29, 2006 4:09 pm

david wrote:This function will return an array of values from of a column you specify through a relationship you specify:

Code: Select all
var array = databaseManager.getFoundSetDataProviderAsArray([name of relationship],'[name of column]')




Thanks David, your method works. However, the results returned are unsorted - in other words, I get something like this:

Code: Select all
036,055,019,002


I think the getValueListItems function automatically sorts the values, but if there is a different way, I'm open to your ideas.

TIA,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Postby david » Fri Dec 29, 2006 4:21 pm

You can sort JavaScript arrays, check under the Array node.

If you need more flexibility (as it looks likes in your case), use a direct SQL query to get whatever values you need sorted in whatever way. Then trigger it when a form is shown or when a record is selected -- basically trigger at the moment you need to use the values.

Comparison:

1- One method with a couple of lines to grab values for only the one record displayed triggered exactly when you need it ...

- vs -

1- One relationship
2- Value list based on a relationship
3- Calculation grabbing the values from the relationship...which will trigger for ALL records -- even those not showing

SQL is your friend. And using Servoy's events wisely is the key to designing a solution that is lean and fast.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Postby LOGIsoft » Fri Dec 29, 2006 5:33 pm

Hi guys,

Thanks to your insight and a little bit of reading and fiddling around, this is what I came up with:

Code: Select all
//  method to get the list of available talks for a speaker
var array = databaseManager.getFoundSetDataProviderAsArray(speakers_to_talks_speakers_join, 'talk_number_lu')
var sarray = array.sort()  //  sorts the array alphabetically
avail_talks = ',' + sarray.join(',')  //  add a leading comma to help in the replace steps below
avail_talks = utils.stringReplace(avail_talks, ',00' , ',0')  // change all leading double zeroes to single zeroes
avail_talks = utils.stringReplace(avail_talks, ',0' , ', ')  // replace the leading zeroes with a space
avail_talks = utils.stringMiddle(avail_talks, 3, avail_talks.length)  // to get rid of the first comma and space


There's probably a way to make it better performance-wise, but it works, so I'm happy with it.

Thanks again David and Marcel. Have a nice weekend!

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Postby david » Fri Dec 29, 2006 8:08 pm

Looks good. The fact that it is in a method and not a calculation is the major performance gain.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Postby david » Fri Dec 29, 2006 8:32 pm

One other thing: to expound on Marcel's suggestion of using a regex replace...here is how that would be done:

Code: Select all
//get array
var array = databaseManager.getFoundSetDataProviderAsArray(speakers_to_talks_speakers_join, 'talk_number_lu')

//sort array
var sarray = array.sort()

//remove leading zeros of each array item with a regular expression
for ( var i = 0 ; i < sarray.length ; i++ ) {
   sarray[i] = sarray[i].replace(/^([0]+)/g,'')
}

//convert array to a string
var avail_talks = sarray.join(', ')


Same result as your code so a matter of preference.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Postby LOGIsoft » Sat Dec 30, 2006 1:43 am

Again David, thanks for the tip. I have to jump in and learn those regular expressions! As usual, there are many ways to skin a cat in Servoy - not that I would ever do that, being an animal lover myself... :lol:

This forum is really a great place to get pointed in the right direction. Hope I can be on the giving end of things very soon, rather than only the receiving end as I am so often now!

Here's something: I found a good place to get started on regular expressions, and I'm sharing it with the community:

http://www.regular-expressions.info

Have a nice evening everyone,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Postby IT2Be » Sat Dec 30, 2006 2:47 pm

Hi Benoit, nice link! But... Beware!

Regular expressions are regular expressions are regular expressions with one important exception. They are NEVER the same for any language. In other words always check them against JavaScript in our case.

I have the o'reilly Regular Expressions Pocket Reference By Tony Stubblebine, Nathan Torkington on my desk. It has only a few pages per language so it looks like overkill but it helped me in many occasions.
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby LOGIsoft » Fri Jan 05, 2007 1:47 pm

Thanks again Marcel for your input - I consider myself warned! :wink:

Have a good weekend,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Re: List of related values

Postby LOGIsoft » Tue Sep 16, 2008 2:06 pm

On a "related" note (pardon the pun): :)

I have a Clients table with related Invoices. Each invoice could be associated with any Representative. Now, my client wants to associate each new invoice with a particular Rep defined in the Clients table - I will use then a lookup to populate the column in the new Invoices, easy.

Thing is, I would like to prevent my client from having to input the Rep info for each one of the thousands of clients they have. Since the related invoices may refer to different Reps, I would like to only get the Rep info from the last invoice produced (based on the date), and put that info in the Clients table for future invoices.

So, my question is: if I use 'databaseManager.getFoundSetDataProviderAsArray' with the correct relationship and Rep ID column specified, how will the array be sorted? Can I make sure that the array obtained is sorted by a second column (the related invoice date column)? I can then use the 'push' or 'pop' functions to get the Rep ID I need...

Also (separate question), is there a way I could obtain both the Rep ID and the Invoice date in the same array? (an associative array?)

Thanks in advance,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Re: List of related values

Postby david » Tue Sep 16, 2008 9:08 pm

One single SQL update statement will do the trick. For MySQL, something along the lines of:

Code: Select all
update table client a set id_client_rep = (select id_client_rep from invoices b where b.id_client = a.id_client order by invoice_date desc limit 1)
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: List of related values

Postby LOGIsoft » Tue Sep 16, 2008 11:27 pm

Thanks David - I see where you're going with that command, and it makes sense. What would the exact syntax be for Sybase SQLAnywhere 10.0.1? Do I run this using the RawSQL plugin? Something like this launched from the Clients form (based on a recent post from Robert Ivens - thanks!):

Code: Select all
var vForm = application.getMethodTriggerFormName();
var vTable = controller.getTableName();
var vServer = controller.getServerName();
var sQuery = "UPDATE TABLE clients a SET id_client_rep = (SELECT id_client_rep FROM invoices b WHERE b.id_client = a.id_client ORDER BY invoice_date desc LIMIT 1)";
var sArg   = "";
    if ( !plugins.rawSQL.executeSQL( vServer,  vTable,  sQuery,  sArg) ) {
       // something went wrong
    } else {
       // need to flush any cached data of this table or else your and other clients won't see it.
       plugins.rawSQL.flushAllClientsCache(vServer,  vTable);
    }

Just a little nervous to run this on live data...

Thanks,

Ben
Benoit R. Savignac, president
LOGIsoft Consulting inc.
--------------------------------------------------------
Servoy Alliance Network Partner
User avatar
LOGIsoft
 
Posts: 260
Joined: Wed May 24, 2006 3:06 pm
Location: St-Basile-le-Grand (Montreal), Qc, Canada

Next

Return to How To

Who is online

Users browsing this forum: No registered users and 6 guests