Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

ODBC Connection with one to many results

Avatar

Former Community Member

Hi there - I have followed Stefan Cameron's excellent blogs on linking livecycle forms to data from an odbc connection however I have now become stuck.

Effectively I have a form that populates part of the form with matching data for a specific id, however I then need to populate a different part of the form from another database connection that holds multiple records for that id.


I have successfully completed the first part (YEAY!) however the second bit I cannot do - I have tried using the xfa.sourceSet.dataconnection.next() however this repeats the same data and doesn't seem to want to get the next record.


What I seem to need is a mix of Stefan's "selecting specific records" (which works great for the first bit) then the retrieving ALL records bit from here

http://forms.stefcameron.com/2006/10/12/displaying-all-records-from-an-odbc-data-connection/

but only where the relation matches the first section.

Does anyone have any ideas - I'm sorry if I am not being very clear - I can post more detail if needed.

Regards,
Bev.

11 Replies

Avatar

Former Community Member

Sounds to me like you need to generate a new SQL query and then deal with those results. If you are merely doing a ...next() after yo uhave gotten a specific record then there are no other records to get (from your last query). You woudl need to generate another query that produces a resultset that has multiple records. You may want to do this with a new data connection (yes you can have more than one) so that you can do the binding of the columns in the DB to the repeating fields on your form.

Hope that helps

Paul

Avatar

Former Community Member

Thanks Paul - I already have two data connections as there are two different result sets I am working on

To clairfy - the first data connection retrieves grant id, title name and address of all grants


The second data connection then retrieves financial data for all projects related to those grants

There are several records of financial data per grant.

So the first bit of code retireves all relevant data for the grant selected.

xfa.sourceSet.claimdata.#command.query.select = concat("SELECT grt_title, grt_contact, grt_org, grt_addr, grt_post FROM gs_user_claim_pdf_table WHERE grt_id = ",sGrtId, ";")

xfa.sourceSet.claimdata.open()

xfa.sourceSet.claimdata.first()

This bit works fine

The second bit is only working for the first record it finds - what I need to do is to loop through all the results and populate a repeating subform with the relevant results but I just don't know how!

xfa.sourceSet.subproject.#command.query.commandType = "text"

xfa.sourceSet.subproject.#command.query.select = concat("SELECT sub_prj_title,claim_amt,tot_est_cost,unit_type,total_units,funded_by,exp_type,grt_id,prj_id FROM gs_user_sub_proj_table WHERE grt_id = ", sGrtId, ";")

xfa.sourceSet.subproject.open()

xfa.sourceSet.subproject.first()

_subprojects.addInstance(1)

xfa.sourceSet.subproject.next()

Any pointers gratefully received!

Avatar

Former Community Member

You shoudln't have to cycle through the records .....if your binding is set up correctly then the product will  do it for you and add the extra instances of your subform to accomodate the addition records. Can you validate that the SQL is returning more than one record? I believe there is a count Also make sure that the subform subprojects has the check box "Repeat Subform for each Data Item" checked on the Biinding tab.

Paul

Avatar

Former Community Member

Hi Paul - thanks again.

Yes the query returns more than 1 result (in the example I keep trying it sould return 3 records)

The repeat subform for each data item is also set.

Do the settings in the ODBC properties need anything specific - e.g. delayed cursor - and what about the BOF Action and EOF Action settings (which I have to say I don't understand) - I am very self taught in Livecycle and boy does it show!

Regards,
Bev.

Avatar

Former Community Member

Yes your ODBC connection should have the delayed open checkbox set. This will cause the conenction not to happen until you set it using the open() property. If you do not then the connection will fire when the form is opened and a single record will be returned. This is what I believe is happening to you.

Paul

Avatar

Former Community Member

I give up! I really can't see what I am doing wrong - I really can't. It all looks like it should work but it blinking well doesn't - thanks for trying to help anyway Paul.


Regards,

Bev.

Avatar

Former Community Member

Hi Paul - I was wondering if I could pick this up with you again - I thought I could washmy grubby little hands of it all, but sadly no, I have to try and resolve the issue and any help would be very gratefully received.

Regards,
Bev.

Avatar

Former Community Member

Sure ...refresh my memory of where we are .....what DB are you using and can you share the

DB with me?

Paul

Avatar

Former Community Member

Well we (and by that I mean ME) are no further along. I am using two MS Access tables. Can I email you the rubbish I have produced so far - you may laugh at your discretion!


Regards,
Bev.

Avatar

Former Community Member

Sure ....send them to LiveCycle8@gmail.com and include a description of the issues. Also you will probably have to rename the Access DB files so that the mail filters let them through.

Paul

Avatar

Former Community Member

Thanks Paul - I've sent them through - hopefully they will get to you

Regards,

Bev.