Linking Tables

[rustylee]

I have recently been using the Flexibility add-on for Macola and have nothing but good things to say about it. It really has opened things up to make Macola do what *we* do. However, I'm seeing some strange stuff. This problem is not limited to application development only, but it also relates to anything report related, i.e., Crystal Reports, Excel, etc.

Generally speaking, in relational database systems, records in separate tables can have relationships based on some kind of identifier or key, which may be one or more fields from each table. For example, The order number and order type fields in the order header file are related to the order line file by the order number and order type fields. Hence a link is created. Additionally, the order line file has another key field, the Line Sequence Number, which is used to enumerate the line items for an order, and it used to relate the table to other tables, such as the Blanket Order Release file and the Order Build file.

Here's the rub. I was creating a small app that checks the order line items and the order build file. The tables, like I said, are related by the line sequence number. I tested the code and it was working properly. I went back to the order in Macola to reset one of the fields my code manipulates. I ran another test and got a different result? Here's why.

When I reset the field in the order, the quantity to ship field, apparently Macola resequenced the line sequence number field. The values in the field were '2' and '3'. I did this on purpose to I could examine the related tables. Now the fields values are '1' and '2'. What is baffling the related tables linked field's values did not change. And yet Macola can properly display the order.

The line sequence number cannot be changed once it has been entered. Since it is a key field, an error 10 will occur if attempted (Pervasive P.SQL). Macola must be deleting the whole line and adding it back or perhaps accessing the data directly, somehow skirting around referential integrity.

In any event, this is troubling not only for developing supplemental applications for Macola, but like I mentioned before, it makes report writing a daunting task as well. Also, in my opinion, this is a wrong-headed relational database management approach.

Can any shed some light on this? Is this how things work with Macola's new MS SQL product?

Thanks in advance.

 

[dgillz]

Rusty,

There is a field called "Unique Sequence Number". This is the number that never changes, while "Line Sequence Number" changes every time a partial shipment is made - essentially what you discovered.

The unique sequence number is the same in both Pervasive and MS SQL environments. Let me know if you have any questions on this peice of your comments

I wanted to followup on your statement of "Macola's new SQL product" are you talking about the "New Macola" aka Macola ES, or are you talking about the same Macola 7.5x with a SQL instead of pervasive/btrieve engine? Macola ES is not even released yet, although as a consultant I have a copy. Macola 7.5 SQL has been out for over 2 years.

Let me know if I can be of assistance in your project.

Don Gilsdorf
Triangle Partners

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[rustylee]

Hi Don,

Thanks for the speedy reply!

I was referring to the 7.6 product. I've only seen the ES product at a users group demo via the web. I should have been more specific.

I'm afraid I don't see a field called Unique Sequence Number in either table (OEORDLIN_SQL / IMORDBLD_SQL), or anything to that effect. I'll email the fields to you.

The sequence number fields in the IMORDBLD file seem to be related to IMORDBLD table itself. What am I missing?

 

[rustylee]

Thanks Don for pointing out to me via email that the field I was looking for is simply named "Line_No", which, by the way, is the name of the field in the IMORDBLD table.

 

[gstrenio]

Will the Flexibility module allow the creation of routines in VBA that can be used to input data into MACOLA and can be used instead of the MACOLA input screens? I have a client who uses features/options screen to select the various options after interpreting a part number. I have written an ACCESS VBA routine that parses the part number and creates an ACCESS table containing the item number, feature number, and option number. To eliminate errors and save time they would like to bypass the manual entry of these options during order entry. Would Flexibility be the proper tool to use to link the ACCESS table and MACOLA input screen?

 

[dgillz]

What do you not ike about the input screens? All of Macola's screens are modifiable regardless if you use Flexibility of not. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[gstrenio]

Thank you for quick reply. Rather than manually selecting options for a feature group, client would like to enter long part number which contains information denoting what options are desired. The external VBA routine would use this number (now entered in a comment field on line item order entry screen) and enter the desired options into MACOLA, obviating manually selecting the options from the features/options screen. Screen would be eliminated, not modified.

 

[dgillz]

That is a good one.

I know that flexibility cannot run a piece of the Macola source code, so it stands to reason that it cannot stop a piece of the Macola source code from running either.

You COULD make these items without an end_item_cd of "F", which would keep these screens from coming up during Order Entry, but it would present a new problem -- how to you assign feature/options to a part that is not end item code "F" in the first place?

Are you a Macola reseller? What has Exact said about this? I am interested in their take on this.
Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[gstrenio]

No, I am not a reseller and I haven't spoken to Exact yet. I have a client for whom I've written Crystal Reports and ACCESS databases that read and use data FROM MACOLA. Some of their tasks would be made easier and less error-prone if data could be entered TO MACOLA from ACCESS database via VBA routines rather than through screens.

 

[dgillz]

You can write to Macola data from Access, VB, VBA, SQL scripts, whatever. Although i am not a programmer, I have done Access update queires many time and in fact I am working on a project to do this right now.

If you would like for us to quote on the flexibility project, or whatever it ends up being, to bypass the screens that are normally displayed at data entry (you would get a piece of the action of course, by marking this up) just let me know.

Don Gilsdorf
Triangle Partners

Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[gstrenio]

Thank you for information. Client does not currently have Flexibility module as part of their MACOLA package and I want to be sure that it will allow them to do what they want done (with the proper programming) before recommending that they purchase it.

 

[dgillz]

Hey gstrenio,

Please give me a call to discuss this project. I have several ideas for you on this. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[dgillz]

Gstrenio,

I was giving this some more thought, and if you are saying that the bill of materials can be derived from the part number, what you are really saying is that you do not what to use the Feature Option Configurator at all on these parts. Is that a fair statement?

If thats the case, lets make this a project to segregate these parts, change the end item code back to blank, and then automatically, based on your set of rules, build the bill of materials.

From then on, its a normal order entry process. You could still perform material substitutions in Macola Production Order Processing on an exception basis.

What are your thoughts on this? Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[gstrenio]

Hello Don,
Your idea would be to make a bill of material "on the fly", so to speak? This would still require an interface between the VBA part number interpreter and MACOLA input screens, wouldn't it? And this would require Flexibility?
At this point the client is considering the costs and benefits of adding Flexibility to their package.
Thanks for your ideas.

 

[dgillz]

Gstenio,

I am not saying that at all!

If that part number in and of itself indicates the BOM, there is ZERO need for a feature/option/configured part.

If you want Flex to build the BOM, that could be doable but you have missed my point if that is what you took from my last post.

My point is you should just build your bills of material, with a one-to-one relationship betwen BOM and item. If Flex can help you with that, go for it.

I thing maknig the item equal to a certain Feature option configuaration is self defeating. Just make it into a bill. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com