View Full Version : Opinion Needed On Design


05-29-2007, 10:48 PM
I'm in the midst of programming a CMMS, by myself and never done it before, and I am in need of some insight. I'm learning new ways to operate Alpha 5v7 everyday but I am still far away from knowing enough to solve design problems. The business I work for needed a computerized maintenance management software that handles every day work.

Stock/Special Purchases by the Parts Department.
Equipment Maintenance Schedules.
Daily Work Orders including Easy/Medium/Estimated Jobs.
There's a lot more involved, but just this small scope is a lot to deal with for me. I guess I'm just sharing this stuff because I'm hoping maybe someone can help in my quest.

Is it reliable to link two tables using an expression involved two fields? It seems obvious any new record on the child table will need the two field values implanted to complete the link. I am using this for my Part Inventory aspect of the program.

It goes like this, All parts have an inventory number (non symbolic, and sequential). This Inventory number is used to link an equivalent part but with either a different Part_No or different Vendor.

Inv No 1:M Part_Id/Vendor (Inv_NO to Inv_NO)
Part_ID/Vendor 1:M Purchases (Part_Id/Vendor to Part_ID/Vendor)
I did this so that purchases for the same Part_Id from two different vendors would show the purchases separately. This also handles equivalent parts but with different Part_ID's all grouped under the same Inv_NO. So Inventory number can actually handle any number of part_ids.

My question is: Is this a suitable way to handle more than one part ID for the same exact part, and also split the vendor purchases for the same Part_ID?
A lot of this I have dreamt up because of various needs vs my lack of knowledge. Can anyone elaborate?

05-30-2007, 10:26 PM
My Purchase table is linked with two fields to the Part table. The Part Table has to have Part_ID and Vendor_ID to make it link. So adding new records that link between Purchases and Parts involves Xbasic creating new records for a purchase and implanting proper values to the Part_ID and Vendor_ID fields. Along the way, any edit made to either the Part_ID or Vendor_Id then automatically loses connection with the purchase records. Unless using Xbasic I ensure all fields are changed and relinked back together, in the simplest terms. What is called when you must follow every link to add/edit/delete any type of information, especially when it's across sets different sets? All these things need to be planned out I suppose and I don't have enough skills yet. The actual Sets are linked in such a way that either you have a concrete link , or an expression of fields and values, or you can just leave information inside of one table. These are the things that occur to me and it gets pretty overwhelming..what's the best medication to help you program clearly...hahah


05-31-2007, 02:17 PM

You cannot be changing the contents of linking fields. If you have a field where you think you might change the contents anytime, do not use it as a linking field. People connect tables everyday with two fields instead of one( I never have and do not intend to). I found it easier to have a field in the master table that connected to other table(s) and the user does not get to mess with this one.

I have not been able to follow your reasoning so far. I will read it a few more times and maybe it will make better sense. The first rule in making a program is to know what you need to accomplish in the end, then work somewhat backward. Put it on paper and figure out how it can work.

My inventory is not pulled out until the sale is final, it gets marked as pending maybe.

It seems you are trying to make a work order and it come out as a estimate or final or maybe. You have an inventory table that you want to deduct products from when sold? You want to track where the parts came from and re-order?

Maintnance is a whole different ball game, but can have a link if part of your inventory has to do with maintenance.


05-31-2007, 07:14 PM
Maintenance is the name of the game...check out other systems like Cybermetrics "Faciliworks" which has a free demo, or "ProView" which recently was bought out by Cybermetrics. These were the founding programs my company started with. Its basically just what you said: A system to create work orders, which are basically a report of Parts used and Tasks used. It's all pretty simple when you think about it, the complications come when ideas are transformed into code.

I am using a unique Part Inventory system that I though of after deciding what was needed in the end didn't result from a One to One or One to Many Link. My problem also is that I cannot express my thoughts clearly at first, I must assume subconsciously that you all have the same viewpoint as me. Ooops #1. :)

If you think about it like this it might help.

A Master Inventory Number (sequential) is used to relate any number of Parts that are equal is all aspects. So one Inv_NO can have say 20 parts linked to the INV_NO. The 20 Parts themselves are Objects themselves and represent the "Actual" part with has an ID. You see, our Vendors (e.x. Steel Manufacturers, Snap-On, Lighting Companies, and such sell their products with a unique Part_ID that they own. Other companies also make the same exact part but with their own Part_ID. So in order to group all similar parts together I created the Inventory Number to group them. My situation then becomes when I log in purchases (mainly for cost and stock count numbers) I have to link the purchases back to the individual Part_ID. The method I chose is to have each Part_Id link to it's own purchases by using the Part_Id and the Vendor who sells that ParT_ID.

To sum it up, Inventory Numbers groups Part_ID's together if they are equivalent, and each Part_ID owns a Vendor_ID. The Part_ID + Vendor_ID combination are the link the the purchase table. So whenever there is a purchase for PartA from VendorA the purchase table itself has to be made absolutely sure that the link information is put into their respective fields. Otherwise there won't be a link.

It's not really a problem at this point, all purchases are linking back to their respective Parts and all Inventory Numbers are showing their children parts as well. I just have a gut feeling that something somewhere is going to throw me off. At this point I make Functions(showing XDialogs) for handling the Adding of Parts and the Adding of Purchases. So far I've had luck.

But are there any red flags popping up about how I am linking and moving information around in my program?

Its getting tricky to keep all the records together, if let's say one of the Vendor_ID's changes because I mistyped something or what to change it. Now I have to change the Name across all of my tables in order for the links to be reestablished. Whooosh. Lots of stuff...

It's been fun working with Alpha 5 so far, and I've learned so much. My company is counting on me completing this project soon and I've only been able to make the Part System work correctly after creating and destroying at least 2 other versions of the system. Hmm....is it common?

Thanks dude,

Tim Kiebert
05-31-2007, 07:56 PM
Hi Daniel,

I am also working on a CMMS. I do not yet have a module for purchase orders because in my particular situation it isn't necessary yet but will include one eventually.

My method of handling different supplier part numbers for one part is that I have a join table between the Parts table and the Supplier table. The Parts table holds info on the part as used locally (ie by you) and includes a part_id field which is auto-increment and internal to my application. The info in the Part table is limited to only describing the part. The Supplier table holds info on the Supplier (clever eh). It has a Supp_id field again only for internal application use as a key field.

The act of aquireing a part from a supplier has its own set of info and therefore its own table(Part_Supp). This is where I store the part number. I also store the price here as it also varies from supplier to supplier. Any info you may want to keep regarding the act of purchasing the part from this particular supplier would go in this table. This table has fields for Part_Id, Supp_Id, PartNUm, Price etc. The table also has an PartSupp_Id field which is an autoincrement field used only as a key field for this table. It has no meaning as far as the data goes.
I then use a set with Part table as parent linked one to many to Part_Supp which is linked one to one with Supplier.

I am not sure how this may help you in linking to purchase orders but thought I would share it in case it might give you an idea. And I will keep thinking about it as well as I want to include this eventually myself. One initial thought is to use the PartSupp_Id field in the purchase order.

05-31-2007, 10:33 PM
Inv_ID describes the part in general.
Inv_ID has #Stocklevel #ReOrdering(min/max) #Any other Key fields

Inv_ID links 1:M to Part_ID using Inv_ID as Key.
Part_ID describes actual Part and Specifics (e.g. #MFG #Model #Vendor)
Part_ID links 1:M to Purchase_ID using exp(Part_ID + Vendor_ID) for the purchase link
Purchase_ID describe any purchase including the P.O., Date, Units of Measure, etc.
Purchase_ID is good for reporting. Use the Inv_No in to show the complete purchase or stock of Inv_ID

Inventory Level: Inv_ID (Inventory Number)
Parts Level: Inv_ID + P_ID + V_ID
Purchase Level: Inv_ID + P_ID + V_ID

Seems pretty cool I think, but that's because it's the best I've done...:)
Now that you mention it, using a sequential number as a link for the P_ID + V_ID problem would make enormous sense. LOL

05-31-2007, 11:01 PM
I don't trust using forms completely when it comes to entering records and ensure proper information. I've been XDialog'ing most transactions on tables. Is this preferable? I've been writing functions to work with records being displayed on the main form, but having them occur from Dialogs and Button Presses. I find myself asking whether it's more efficient to work with tables directly outside of the form, or to work with what's being displayed on screen. If that makes any sense that would be nice.

Is it good to manipulate records with Xbasic over using built-in operations and field rules?

Thank you so much for responding

06-03-2007, 02:13 PM
I don't trust using forms completely when it comes to entering records and ensure proper information.

I use forms more or less exclusively for data entry. They work. However, I generally use dialogs (as verses xdialog) for data entry. I make the underlying form modal to prevent data entry, usually w. an embedded browse to allow viewing records.

I've been XDialog'ing most transactions on tables. Is this preferable?

If you like xdialog, I think that is a preferable method in that it lets xbasic address the live data, not the user.

I find myself asking whether it's more efficient to work with tables directly outside of the form, or to work with what's being displayed on screen.

Addressing tables is better than letting the user "touch" the data, in my view.

Is it good to manipulate records with Xbasic over using built-in operations and field rules?

Again, this is preferable in my view.

06-03-2007, 04:14 PM
That makes me feel a lot better because I thought that I just liked doing things the hard way. :) I would rather dictate what the user can do with records, and to ensure nothing unexpected occurs, than work completely with field rules and form rules...

Thanks Petah

06-04-2007, 12:35 AM
The other aspect of my program deals with Dated events. I have an idea but it just doesn't seem right. I will use a Master Table that simply records dates (perhaps every day that my program is run) and this Master Table will be the basis of a set. The Children in the set, (linked by Date) to the Master Table would filter everything in my program to a date. The areas affected by this Master Table, would be available records such as "Work Orders", Dated events such as Maintenance Scheduling, and anything else that happens on a Day to Day schedule. So if I used this Master table which stores "Program Run Dates" and match that with things such as Dated Work or Dated Events...does it sound like something that would work or it too simple and I got it all wrong? What I'm looking for is a program that manages day to day work, totals, comparisons, and record keeping. This new idea just dawned on me so if you have any suggestions I would be so appreciative...thanks


06-04-2007, 12:06 PM
I do almost everything in forms including data entry/edit. I still have some field rules to help a bit. I have had no problem with data to this point. I DO trust the forms and my code written for use in those forms.

When programming in VB, Clipper, etc., we made variables for all fields we wanted on a form and wrote the code to update or enter those in the table as needed. This system created as many problems as it solved, but was workable. This can still be done on a form today in alpha. I see no reason to change what I am doing to this point and go to variables. May change my methods later?.?.