Problem with embedded browse

[seaken64]

I'm not sure yet how to explain the problem. I think if I could explain the problem better I may be able to figure out what needs fixing. But alas, I am spending my days and nights trying to run my business and haven't really learned what I need to know about programming in Alpha Five. I was hoping I could just click and place and it would work. Okay, here goes my attempt at explaining what I need help with.

Up until yesterday I had a working embedded browse on my main Customer Management form. This browse would show me a list of all "Units" owned by the Customer when I clicked on the Units tab. This form is based on a set that starts with my Customer table and links several other tables that hold information about several aspects of what I track for each customer. I have a Request table to log customer requests, a Transaction table to log monies received on Invoices or Contracts, and a Units table to hold records for each separate appliance owned/purchased by this customer, including where it is installed, it's serial number, etc.

BTW, this database was started before I had version 9. I now am using it with Version 11. Version 9 has been removed. All my edits are being done in Version 11.

My form consists of a main information area that holds the customer number, name, contact info, etc. Under this main area I have placed a tabbed sub-form with a tab for each of the linked tables. One tab holds fields for the Customer Account info, one for the Requests, one for the Units, and one for the Transactions. By clicking on each tab of the subform I can quickly see all records associated with this one customer and each table is linked to the Customer table with a CustomerID field.

Yesterday I tried getting fancy and added another embedded browse to the requests tab, under the main requests browse, that would show me the linked Units for each request when I clicked on the specific Request record in the requests browse. I couldn't get it to work at first so I figured out that if I edited the set to include a link from the Request table to the Units table I could get the correct unit to show up. Great! It now works.

Unfortunately, now when I click on the seperate Units tab to look at the Units browse I now have a bad browse. The broswe now shows me a continuously repeating list of the same Unit number. It used to show me a list of all the Units owned by this customer. Now I only get one record repeated down the browse, filling the browse from top to bottom.

I tried removing the browse and redoing it. I tried creating a named browse and then linking to it. Nothing is working.

I have a feeling it has something to do with the set definitions. But I really don't know. It could be completely unrelated.

Is it okay to add more than one link to the same table in a set definition? The first link is from Customers to Units, as a One-to-many. I also linked the Units table to the Request table so I could see the model number when I clicked on a request in my request browse. So now I have the Units table linked twice in different branches of the set diagram and one of the links is named with an alias of "Units1" and the other is "Units".

I'll try to lay out my set diagram here:

Customers
|===Requests
          |===Request_Dtl
                    |___Units1
|===Transactions
|===Units

What have I done? Did my change to the set diagram break my browse of the Units records associated with each Customer?

Is there a way to copy this database/workspace and remove all the personal data at the same time? If so, I could send a copy of my database so you all could take a look at what I've done. If not, I'll try to explain the best I can.

Thanks,
Sean

[seaken64]

Re: Problem with embedded browse

Okay, I may have confirmed that the problem comes from adding the second link to the same table in the set diagram. I went ahead and deleted the new link I had created previously and now the Units tab browse is back to normal.

I guess I don't really understand why I can't get the fields I want in the embedded browse on the other tab, the Requests browse.

For each Request record I can have multiple linked Units. I used a linking table I named Request_Dtl. Each record in this linking table holds the Request number and the associated Unit number. This works to have a Many-to-Many relationship between the Request table and the Units table. What I was trying to do was show all the associated Units for each Request as I clicked on each Request in the Request browse on my tabbed sub-form on my Customer Management form. I tried to add an embedded browse to show the Units linked to the Request through this Request_Dtl table. Unfortunately I caused a problem with the more important Units browse on the other tab (the Units tab) on my sub-form.

Here's my current set diagram after removing the second link to the units table (Units1):

Customers
|===Requests
          |===Request_Dtl
|===Transactions
|===Units

I think I have to totally redo my sets and forms. But for now I was hoping to be able to add a couple of features to my existing format. I am thinking I may not spend much more time on this and instead start figuring out how to do all of this record keeping using the web side.

Thanks for the help.

Sean

[johnkoh]

Re: Problem with embedded browse

Sean,

I guess I don't really understand why I can't get the fields I want in the embedded browse on the other tab, the Requests browse.

Is the Browse and the field you want to insert same table?

[seaken64]

Re: Problem with embedded browse

No, and I guess that is the problem. The field I want to see is in the Units table and browse is on the Request_dtl table. My set does not have a link to the Units table through the Requst_dtl table. When I attempted to create such a link I broke the other browse that was based on the same Units table but linked through the Customers table. I guess I can't link the same table more than once in a set design.

Maybe I can do what I want using the programming language? I've always just used the menus and ActionScript to get the results I want. This time I think I stretched too far. I'll just put it back the way it was.

Thanks

[johnkoh]

Re: Problem with embedded browse

Sean,

My set does not have a link to the Units table through the Requst_dtl table.

You can use the Lookup(x) or UDF to call the field values from other tables without link(set).

I've always just used the menus and ActionScript to get..

I still do but You have to know how to use the xBasic in order to get full option. You may not able to make something what you need without xBasic.

[johnkoh]

Re: Problem with embedded browse

I guess I can't link the same table more than once in a set design.

Yes, you can link more than once and the field and source of table name(alias) are required your attention when you apply it.

Most of experience guys are recommended that make the small size set, 2 or 3 max if it is possible.
If you need 10 tables link together, do not make one huge set. Breakdown to small size.

[seaken64]

Re: Problem with embedded browse

Thanks John,

Yes, I have read that before. Problem is I don't know what tools to use to link tables together without using this 10 table set design. Still got lots to learn.

Can you give me a tip on how to use a lookup to do what I am trying to do with a set link?

Sean

[johnkoh]

Re: Problem with embedded browse

Sean,

I have enclosed some of the Screenshots. I only show the lookup function and did not mention about UDF or using the variable method at all. I hope it give you the general conception(?) of how to get the fields from other than linked tables.

(1) Other tip: make a Study Db and actual your project DB. Before you make a application, practice from your Study DB. It will help you will not mess your project DB

(2) You can use the Lookup() function on Table level, set level, forms or else.
(a) When you write it on the Table or Set level, you can find it at Report's dropDown lists.
(b) When you use it on form level, you can see it only current form. You can copy and paste it to other forms.

Tutor_1.jpgTutor_2.jpgTutor_3.jpgTutor_4.jpgTutor_5.jpg

[seaken64]

Re: Problem with embedded browse

Thank you John. I will study this. Right now I'm too busy to program. I'll come back when I can.

Sean

[SNusa]

Re: Problem with embedded browse

Using DBF's try this function instead and search the external table:

dim SHARED vc_LookUpValue as C ' shared/available anywhere on the form.
vc_LookUpValue = lookupc("C","FindThisValue","ReturnThisFieldValue","FromThisTable","UsingThisIndex") ' First Parm "C" for closest mach
vc_LookUpValue=alltrim(vc_LookupValue)

Asuming you're trying to look-up a value in table B, using a field value in table A (to embed the lookup value in a browse using a calculated field): The FindThisFieldValue parameter can be written like this:

"TableA->FieldName".....

[B]cc_LookUpValue = alltrim(lookupc("C",TableA->FieldName,"ReturnThisFieldValue","FromThisTable","UsingThisIndex"))[/B]



'NOTES:
'Note1:  cc_LookUpValue the cc+ designates a "calculated character" field. (it's the way I differentiate calculated fields from other variables
' (I turned it into a "one liner" above, so it may be used as a "calculated field" on a browse.  ~  Alltrim is necessary to remove spaces.)

'Note2: It's also a good idea to keep index names to a max of 8.  I take the field name, remove all vowels, and add 2 underscores at the end.
'  Example:  CustomerID field index would be named "Cstmrd__"  (It's easy to glance at and know it's my designated index instead of
'an alpha generated one.  Also, the 2 underscores indicate it hasn't been truncated.  (a5 bug can cause unexpected index truncation)

'Note3:  Same goes for table field names:  Keep them to a maximum of 10.

Lookupc() is faster as it uses an index. (which must be pre-defined on the table)
It doesn't matter where the table is, or whether it is joined/part of your layout's set... Give it the parameters, it will return the value.