Alpha Video Training
Results 1 to 11 of 11

Thread: Opinion Needed On Design

  1. #1
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Opinion Needed On Design

    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?

  2. #2
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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

    Dan

  3. #3
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Opinion Needed On Design

    Daniel,

    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.

    Dave

  4. #4
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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,
    Dan

  5. #5
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Opinion Needed On Design

    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.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  6. #6
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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

  7. #7
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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

  8. #8
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default Re: Opinion Needed On Design

    Quote Originally Posted by warmexxus View Post
    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.

    Quote Originally Posted by warmexxus View Post
    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.

    Quote Originally Posted by warmexxus View Post
    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.

    Quote Originally Posted by warmexxus View Post
    Is it good to manipulate records with Xbasic over using built-in operations and field rules?
    Again, this is preferable in my view.

  9. #9
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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

  10. #10
    Member warmexxus's Avatar
    Real Name
    Daniel Murphy
    Join Date
    Nov 2006
    Location
    Worcester
    Posts
    117

    Default Re: Opinion Needed On Design

    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

    Dan

  11. #11
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,026

    Default Re: Opinion Needed On Design

    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?.?.

    Dave

Similar Threads

  1. Table design help needed...
    By djldc in forum Alpha Five Version 8
    Replies: 50
    Last Post: 05-11-2007, 09:21 AM
  2. Opinion Of Set Design
    By warmexxus in forum Alpha Five Version 7
    Replies: 4
    Last Post: 01-06-2007, 09:21 AM
  3. web design help needed, I think
    By Wanda Tucker in forum Alpha Five Version 5
    Replies: 1
    Last Post: 04-12-2004, 07:48 PM
  4. Backorder design advice needed
    By Pat Bremkamp in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-22-2004, 09:56 PM
  5. Design Help Needed
    By Tom Patten in forum Alpha Five Version 4
    Replies: 6
    Last Post: 02-20-2001, 09:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •