Complex GoTo Browse Record

[J.R. Noe]

I have a form with 3 browses. One browse has a table recordsource consisting of 3 fields, RecID, PathName, ParseName. These are all pieces of sheet music on the users computer.

Another browse has a list of gigs (shows), and the 3rd browse contains the sets for each gig. See attached.

Users add pieces of sheet music from the browse on the left to the browse on the right by clicking the "+" or double clicking the name. This adds the piece, and the id of the gig, to the browse on the right. This works fine, however, i want the piece to remain highlighted in the browse on the right after adding it. This works sometimes, and i think it is due to only using the file id from the browse on the left and not including the gig id from the browse at the top. Since the browse on the right can contain thousands of records with many duplicate pieces of sheet music, but all belonging to different gigs, i think the problem lies with it utilizing an id not currently showing in the browse on the right. Any ideas how to use 2 criteria to create a record pointer?

Other Info: This form is built on a set with 3 tables, Gig (with all gig or shows), Charts (this is the complete sheet music library obtained by reading from a directory), and GigCharts (this is the table containing the sheet musi for each gig/show) To make this work, the Gig table and the GigCharts table is linked by the GigID. But to make the library table work, i created a common field in the Gig table and the Charts table. These 2 tables have a default id of "X" so all the charts library will show for every gig (Cartesian product).

Any help will be greatly appreciated.

[Mike Wilson]

Re: Complex GoTo Browse Record

JR,
you are contradicting yourself. first you say: "3 browses. One browse has a table recordsource". Then you say: This form is built on a set with 3 tables, Gig (with all gig or shows), Charts (this is the complete sheet music library obtained by reading from a directory), and GigCharts.

"Since the browse on the right can contain thousands of records" Browses don't contain records, tables contain records. Which table is that Browse viewing?

It would be most helpful if you provide the set structure that tells what the parent is and what the children are, with link (1:1 or 1:M or as you did with describing all-to-all) or show screenshot of the set, and then identify the browses by the table they are viewing. Also, I would need to know what is happening when you press the "+".... ? copying a record from one table to another? Querying the second table based upon the record in the browse selected?? What?

[J.R. Noe]

Re: Complex GoTo Browse Record

I didn't intend to be contradictory, but i guess i do not understand the Alpha terminology yet.
Are tables no longer refered to as tables when they are contained in a set? And since you can view records in a browse, I mistakenly stated that it contained records. A query or an sql view is referred to as containing records, even though those records are in a table. I come from an Access / .NET so I am still in that mindset. I still have the learning curve to overcome, but I'll get there.

The "Other info" at the bottom was meant just to give some additional info on the structure, but that is probably what made it even more confusing.

Here are some more shots showing the structure and tables.
The form is created from a set consisting of 3 tables. All 3 browses have a recordsource of one of those tables (3 browses/3 tables). The gig table and the gigcharts table are linked by the gig_id in the gig table. The gig and the charts tables are linked with the many to many with fields with a default value of "X".

Pressing the "+", or double clicking the file name in the browse on the left, creates a record in the gig_charts table with the chart name, chart id from the browse on the left, and the gig id (this is obtained from the gig browse in the upper right).

browse on left - charts table
browse on right - gigcharts table
browse upper right - gig table

[Mike Wilson]

Re: Complex GoTo Browse Record

Well,
The only way I know how to achieve this WITH an alpha sort for the chart_name on the gig_charts table is with a loop that steps through the records in the browse for the gigs table testing for the gig recno and then the same for the browse for the gig_charts table, testing for the chart_id, until it finds the record to end up on. See example attached. Having a base order on the form does not work, seen by the gig_charts browse not refreshing when a chart_name that is before the first listed is chosen. So I placed the sort expression in the function that copies the record. This strategy of stepping through the records and testing them may overall not work for you because if you have many many records per each gig, or many, many gigs, it might be too slow.

[J.R. Noe]

Re: Complex GoTo Browse Record

Thanks Mike, I have an idea but tell me if it is the wrong way to go.

if i create a field in the gig_charts table and concatenate the file_id and the Gig_id fields into this new field, that will give me a unique id to point to (file_id from the browse on the left and the Gig_id from the browse at the top). When the new record is added by double clicking or the "+" button, it can use the concatenated field to find, and highlight, the new record.

Usually there are anywhere between 20 - 70 numbers per gig, 70 being a very long gig reserved for special shows. If i do it the way i described above, i am concerned about the speed as the tables get larger.

[Mike Wilson]

Re: Complex GoTo Browse Record

it can use the concatenated field to find, and highlight, the new record.

Great thought. When you find out how to get to a child record with a find by (indexed) technique, please let me know, because I don't know how this is accomplished. You can use things like topparent.recno_goto() all day long, but that only works for the parent table record, not at the child table record.

[J.R. Noe]

Re: Complex GoTo Browse Record

Thanks Mike, I will keep you posted of my progress, or lack thereof. You did a good job of throwing that gig app together in that short period of time. It took me a lot longer to get that much done, but I am learning,....I am learning.

I am going to try the concat field idea, worse I can do is fail, and that is nothing new for me. - J.R.

[Mike Wilson]

Re: Complex GoTo Browse Record

I think you might rethink your set structure to acconplish your goal. If you make gig_charts the parent, you can achieve what you are seeking. See attached. The form it will open to is based upon a set with the gig_charts table as parent. This allows you to use the topparent.recno_goto() function to focus on the newly added record. Some other obstacles needed figuring out but it seems to be working as you desired. i threw in a couple progressive lookups in case you might like to use those.

[J.R. Noe]

Re: Complex GoTo Browse Record

Standard, Eb drop D? Sounds like someone is a musician.

This is pefect, thank you Mike, with just a quick look I think this is exactly what I was wanting to do. When I first started, I thought I tried different structure to see which one would work best, and I missed this combination all together.

I really appreciate all the time you put in to this helping me with this. - J.R.

[J.R. Noe]

Re: Complex GoTo Browse Record

Mike, this worked out great and just how i wanted it to.
Thank you. Just one more question about it. How can i get the browse on the left to remain on the current row after clicking the add button? It wants to go back to the first row, and if you are going down and adding records, you have to start all over again. I searched the forum but since this is a child browse, i could not find a solution that worked. Thanks.