Adding Foreign Keys to a Browse

[DonnEdwards]

Forgive me if I have missed this in a lesson or documentation. I’m still feeling like a baffled newbie.

I have a “Status” table that I want to use to show the status of a “Job”. The “Status” table is a simple lookup: “status_id” is a long, “status_name” is a string. There is a 1:Many relationship with “Job” , and it also has a field called “status_id”.

On the browse form I would like to replace the “status_id” column with the relevant “status_name” instead. “Open”, “In Progress” and “Completed” are more user-friendly than 1, 2 or 7. Ideally I would like to be able to automate this, rather than hand-code the change on the browse form.

Where can I find some instructions on what needs to be changed in the Jobs browse?

[Clarion_clarion]

in the DCT define Relation between 2 tables.

In app - just join two tables in Data tab and select filed(s) you need in the List Box Format:

and clarion will generate all for you:

BRW1::View:Browse    VIEW(CWAREA)
                       PROJECT(ARE:Code)
                       PROJECT(ARE:Description)
                       PROJECT(ARE:SysRef)
                       PROJECT(ARE:CompSysRef)
                       JOIN(CCO:BySysRef,ARE:CompSysRef)
                         PROJECT(CCO:Code)
                         PROJECT(CCO:Name)
                         PROJECT(CCO:SysRef)
                       END
                     END

[DonnEdwards]

Thanks. It was staring me in the face

Is it possible to automate this? I have many foreign keys

[Clarion_clarion]

automate

what do you mean? If you have Relations defined in DCT you can join as many tables as you want.

Even if you have no Relations - you can manually join!

[DonnEdwards]

I mean do I have to change each browse form manually? It’s a lot of repetitive work that should be possible to automate. This app has only 9 tables but already there are 11 foreign key lookups. There will probably be more before I’m done.

I would have expected the Application generator to do all of this for me, given that the relationships are already defined in the dictionary. But it doesn’t, so I have to do them by hand. What will I do when I have 50 tables and 125 foreign keys?

[Clarion_clarion]

do I have to change each browse form manually

yes…

[cwsumner]

I think I understand you, now. The short answer is no, most of us do that part manually.

However, there is a thing that can do this, if it was originally set up using the standard templates. (Browse, etc.) This thing is called Synchronize Application (different from Synchronize Dictionary downloading), it changes Generated code to match what has been changed in the Clarion Dictionary.

Be careful though, and make a backup of the app file first. It can mess things up if hand changes, to template settings, have been made. And in old code such things have probably been done, so it is dangerous. It also does not change hand-code embeds.

You can also Synchronize one procedure at a time, see Procedure and Window properties menu.

If you are making a new program that you did “by the book”, then it should work. But again, backup your files first.

[JonW]

Although you have set up in the dictionary how the tables fit together, there is still really not enough information available to the appgen to say: if I add table x to the browse, then I should also always add tables y,z and q to the browse that have 1:many relationships with table x. And further, that instead of adding column lookup_id to the browse I should add column lookup_value from table z. You probably could make a template to make clarion behave like that, but you would probably end up spending as much time removing stuff you didn’t want, as you would save from having stuff that you did want there.

Something you might consider is creating views on the server side (if you are using SQL), that add in the lookup values as though they are table columns. It’s particularly useful if you have to look up into the same table multiple times, because it avoids the messiness of having alias tables in the clarion dictionary.

[Bruce]

This thing is called Synchronize Application

The menu items exist in C7+, but as far as anyone has been able to determine there’s no code behind this, and it doesn’t do anything anymore.

[Bruce]

Although you have set up in the dictionary how the tables fit together, there is still really not enough information available to the appgen to say: if I add table x to the browse, then I should also always add tables y,z and q to the browse that have 1:many relationships with table x.

yeah, there kinda is…

You probably could make a template to make clarion behave like that,

This is exactly what the NetTalk Browse Wizard does. But for Many:1 relationships. So if you have “CountryCode” in the table being browsed, this links to a Country table lookup, which in turn then means you want the Country name in the browse, not the country code. This is done for you automatically. But I agree that 1:Many relationships cannot be inferred in this way.
the NetWebForm wizard is equally as smart, automatically creating lookups, displaying description instead of value, and so on.

but you would probably end up spending as much time removing stuff you didn’t want,

Turns out removing browse columns is really fast, much faster than adding them. So the NetTalk wizard errs on the side of adding more rather than less.

None of which is helpful to Donn of course because he’s doing Desktop, not web, work. I’m just mentioning it here because it’s not only “theoretically possible” but is indeed working in real-life, albeit without ABC Desktop support.

Incidentally all it takes for this is a smarter Wizard - something that could be done by anyone. There are no changes to the underlying templates required.

[jslarve]

Incidentally all it takes for this is a smarter Wizard - something that could be done by anyone. There are no changes to the underlying templates required.

Does it eat acorns, by chance? < g>

[DonnEdwards]

None of which is helpful to Donn of course because he’s doing Desktop, not web, work. I’m just mentioning it here because it’s not only “theoretically possible” but is indeed working in real-life, albeit without ABC Desktop support.

Fortunately I will be moving this project to NetTalk as soon as I can.

I have been using an add-in for Microsoft Access 97 called Mustang, that “knows” about foreign keys and other things and makes it really easy to change your mind and add in tables or fields as you go. It also allows you to add other properties to a field in the dictionary, so that you can say “this is a phone number, so format it as such and validate it accordingly”, or “This memo field should be 80 characters wide and 5 lines deep on the edit form, but don’t show it in the browse form at all”

I think I should do a ClarionLive talk about the things I miss in Clarion that I have used in Mustang for the last 20 years. Maybe we can develop some wizards that do this stuff. I certainly plan to do so once I have grasped the basics of Clarion and can tackle the more advanced topics.

I really appreciate the help and advice I am getting while I learn the basics. I also bought Bruce’s Kindle book “Programming in Clarion’s ABC” which is helping me to understand a lot of stuff.

[Bruce]

It also allows you to add other properties to a field in the dictionary, so that you can say “this is a phone number, so format it as such and validate it accordingly”, or “This memo field should be 80 characters wide and 5 lines deep on the edit form, but don’t show it in the browse form at all”

All of that exists in Clarion’s dict as well.

[Bruce]

I think I should do a ClarionLive talk about the things I miss in Clarion that I have used in Mustang for the last 20 years.

I’d recommend coming to the OpenWebinar on Wednesdays and working through the list. I’m sure there are things missing - but there are also lots of features that go unnoticed because they don’t get mentioned a lot, or people don’t notice them or whatever…

[JonW]

Hi Bruce,

In the “not enough information” comment I was thinking of things like:

if your lookup tables contain a language code as well as the lookup value, where the language code limit is a global variable

in a typical company/order/orderitem type system where your main table is orderitem, the order table is 1:many to orderitem, but you very likely don’t want to use any of the other columns from order (date, salesperson, whatever) in your browse; just the order_id which is in orderitem is fine.

And if your main table is Order, then you may have many columns in the company table; certainly it’s easy to add them all, but probably you only want to have the company name there.

Nice to know there’s a wizard out there that does that stuff.

Jon

[Bruce]

Hi Jon,

Sure, there are always going to be situations where there isn’t enough information, but for many (most?) cases the information is there. For example Many:1 “lookups” are pretty straight-forward. I only populate the one field from the lookup - but that’s enough, and of course it’s trivial to add more if necessary.

cheers
Bruce

[cwsumner]

This thing is called Synchronize Application

The menu items exist in C7+, but as far as anyone has been able to determine there’s no code behind >this, and it doesn’t do anything anymore.

That’s interesting… Ignore that part, then.
I work in many versions, one client is even still in C5.5, so it’s possible I have not tried this in C11.
Since it is still in the Help, maybe they are still working on it?

[DonnEdwards]

All of that exists in Clarion’s dict as well.

Gosh, more stuff to discover. I guess my examples weren’t good enough. I have a routine to format and check SA cell phone numbers, and SA ID numbers. If the user puts in a bad number I set the field background colour magenta. (In the edit form, not the browse)

I’ll put together a presentation in Powerpoint and we can discuss it in a Wednesday meetup. I’m sure I’ll learn a lot in the process.