Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 Difference Analysis For Bill of Materials

Author  Topic 

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-10-25 : 14:26:27
Conceptually this is actually a huge problem to solve but don't let that stiffle, ideas no matter how fragmented or outrageous are greatly needed.


This isn't the classis BOM type situation as the assembly/subassembly aspect of it isn't really an issue. It's a versioning issue where design is issuing itemized lists to purchasing on multiple occasions.


The list comes from a concurrent design to build environment so the first issue to purchasing may come as a two item list. This same list then may appear as having those original two items plus 48 more. Purchasing knows to purchase the new 48, but may also be responsible for a change (quantity or quality) to the original two.

So I'm presenting a kind of superpowerful Windows.forms.datagrid
that allows design to build and edit a cross reference catalog as well as do fuzzy lookups on purchasings item master. This lets them automate much of their CAD generated materials list, as well as clean it before sending it to purchasing. That part works pretty good(or will one day but at least I can see how it will work).

The part I don't have figured out right now and am kind of stuck on is how does one represent in an interface the differences between sets; or the red and green lines from VSS.

So

Engineering current BOM shows
2 BUY 2.000 CAVITY BLOCK P-20 T.F. 7 X 18 X 28

Engineering last version shows
2 BUY 2.000 CAVITY BLOCK P-20 T.F. 7 X 18 X 26

And it could be possible that Purchasing has this in their ERP
2 BUY 2.000 CAVITY BLOCK P-20 T.F. 7 X 18 X 27

(this would not be a usual occurance as in general the
Purchasing would be the same as engineering "last version" but not always and I still have to do a last vs. new at the purchasing level anyways)

Consider the leading 2 as a primary key in this single case.

So I don't know how to make a GUI that allows display of the
changed rows. I can get the data which represents all changed
but I don't know how to present it to the user so they can make
desicions on what changes they are seeing, the validity of the changes, wether they are meaningful or not. Then eventually move on to manipulationg the various updates back to the audit and current tables. But I'm kind of stuck on the interface right now. What interface is simlar to this? Thoughts?



2,BUY,2.000,CAVITY BLOCK,P-20 T.F. 7 X 18 X 28
3,79 more items here

                                               2,BUY,2.000,CAVITY BLOCK,P-20 T.F. 7 X 18 X 28
                                               6, at least 5 more items here

2,BUY,2.000,CAVITY BLOCK,P-20 T.F. 7 X 18 X 26
3,74 more items here

                                               2,BUY,2.000,CAVITY BLOCK,P-20 T.F. 7 X 18 X 26
                                               9, 1 more item here

2,BUY,2.000,CAVITY BLOCK,P-20 T.F. 7 X 18 X 27
3,73 more items here



Something like this is what I'm leaning to
(again the leading int in each item is a key)

Esentially what is ended up with is five lists (I'm not saying they all have to be on the same page/tab/panel)

On the right is the set list of items with the differences highlighted. typical counts on the left would be

Top list 80

middle list trailing behind a little maybe 75 items

bottom list maybe 74 items just because purchasing may not know
where to get something

so the top right list will contain at least 5 items in the list plus any that are different

the bottom right list would have just the 1 item as there is no new version yet submitted by design. just the legacy of last engineering version (75 items) and the purchasing work accomplished on the basis of that version (accurate ordering in the ERP table equivalent to last engineering - 1 (<- the one they were stumped on.) Obviously once the designer submits a new revision things change. Purchasing will now have more action items so bottom right list would be larger. top right list might have none if no design is done after an new BOM version is issued.

Helps to write this stuff down, So I'll just keep expanding on this.
they goal is to see the GUI layout that would make sense to both the design team and the purchasing folks.
SO 28 would be design self auditing "wow I changed that"
AND 26 would be purchasing contemplating that they had effectively ordered the wrong size.


A key concept here to any of those that have worked in a design environment is that the item numbers need to be consistant through the build. Traditionally design drawings have based their purchasing on balloon numbers materials lists. That's fine if your drawing is a single deliverable that is a logical predecessor to actually build the thing but it falls down qick in a concurrent design build environment. So there has to be a divorce of the balloon numbers from the drawing and what you get is an inprocess identifier that I'm going to introduce to the design environment as a build balloon number. Final deliverable design will need that detail cleaned up to meet presentation level standards but still worth it. I'm pretty stoked about this project.

I wish someone would start an Official XML Rant Thread.

jhermiz

3564 Posts
Posted - 2005-10-26 : 10:04:41
I could probably sit here and try to figure out your business logic, but that would be a waste of time...since I don't work for your company. I did not read your entire problem because it was lengthy and I despise lengthy posts...however, I must say I have run into a similiar issue in the past.

The problem I had was also a BOM for our business development group. Without going into full details I had a versioning problem, with one specific "in-process" row while many "version" rows (past updates / sales).

What I ended up doing is having a data grid with the current version that is used. I then used a tree like structure and had a plus symbol if the row included versions. If it did the end user would be presented with the child rows of versions of that parent. It was ordered by the VersionDate DESC so that it had the most recent first.

To make it a bit more slick, I added colors to each row to help signify the meaning of the row, with various shades and a key to indicate to the user the most recent, the oldest, etc.

Although to this day BOM's are a problem, it fit well for our situation. But keep your hopes up because you are not the only person with these types of issues. BTW, I would recommend an interface book, but most interface books I've read hardly go into any detail about BOMs...and the ones that do present too little data on.

Good luck,
Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-10-26 : 10:53:29
Thanks jhermiz.
Were you ever successful in changing the rolled up datagrid row(current version with plus sign)
formatted in response to recognizing a delta in the underlying tree?

For item 1

1, A (current)
-1, A (last)
-1, B (oldest)

rolled up would look like this
+1, A (current)


1, A (current)
-1, B (last)
-1, B (oldest)

rolled up would look like this
+1, A (current)



I wish someone would start an Official XML Rant Thread.
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-10-26 : 11:36:24
I'm not certain of what you're asking, a good example would be much appreciated...

Let me give you a bit of what I did..

I think the issue you are dealing with is versioning, but let me tell you what I did and it might help shed some light.

In my case I dealt with proposals being created for our business group. The issue at hand is versioning of a proposal, at any time they may go back to a previous proposal because the dang customer is always picky.

Lets say the customer is Ford. Ford wants us to build an assembly line for their hot new mustangs. So rather than creating a table with just "Active" proposals and so on and so forth. I decided that every proposal is a valid proposal, whether it is active or not. I did not want to create a history table, why, because it was not needed, in reality the versioned proposal is still a proposal. What I ended up doing is creating one ultimate table to hold all proposals, and linked it to another table which held the most active proposalID along with the version number for this proposal.

When I joined the proposal table, with the table of "good" proposals I simply would link the two and obtain the relevant information. The forms no longer displayed the "In-Active" proposals since the join only returned the active proposals.

Now the user looks at his / her proposal, and has gotten word from Ford that they would like to revert or go back to a previous version, say 1.1, we were originally at 1.0, reved it to 1.1 and reved it again to 1.2. 1.2 is the latest revision, but ford looking to save costs on their line have decided against some additions and would like to go back to 1.1. I simply store 1.1 back into my "good" table and join back to my proposal table. 1.2 is no longer available to the user, he is now looking at 1.1. I don't have to worry about loss of data, since 1.0 and 1.2 records still exist. Note that there is much more complexity in that I have child records that poin to 1.0 proposal, and DIFFERENT child records that point to proposal 1.2, as well as 1.1.

Ford looks at proposal 1.1 and likes it but decides to add more detailed information, enough to revise 1.1 into 1.3. What happens here is the user clicks some sort of revise button, gets the latest rev number 1.2 + .1 = 1.3. This is actually ANOTHER proposal, and all of the related information is copied over. So although that seems like a lot of data, I had to do this because at any time the user may want to go back and revert to an old proposal.

The proposal table now looks like:
1.0
1.1
1.2
1.3


But the good proposal table only stores the proposalid and the rev number of the active proposal, 1.3 now. All related information is still maintained since we have referential integrity with all the child tables. Finally Ford, agrees with version 1.3 and purchases the line.

My point here was, sure we have a lot of data, and there is a lot of business logic and process to revise a proposal. But my interface flowed pretty well.

The interface basically showed those revisions in a grid format, where the active was the root of the tree, and the child records were just nodes. Double clicking on a node gave you a more detailed representation, and even allowed for users to play with the numbers without saving. If they wanted to go back to a previous version they would simply committ the transaction, and believe it or not it was one simple update, updating the good table with the proposalid and the revision number, absolutely nothing else.

So for a revert, going back to a version, it was a simple one update. The revision was the transactional activity where I would actually copy all of the current proposal data, along with ALL child records for that proposal, and then assigned it a new revision number. Take my example of 1.1 going to a revision to make 1.3. 1.3 ended up being exactly what was 1.1 and any add ons by the user.

Data was always there when and if it was needed. We didn't worry about losing the data because it was all stored along with the child data. If we needed to drop data, we had a process of what needed to be dropped, but we rarely if ever did that. It remained there for us for history.

Our processes may be a lot different than yours, but to be quite honest most corporations work much the same way in an organizational structure : Logistics, purchasing, engineering, marketing, etc.

Hope this helps.

Jon




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-10-26 : 11:38:36
Sorry in my above post I said...

When I joined the proposal table, with the table of "good" proposals I simply would link the two and obtain the relevant information. The forms no longer displayed the "In-Active" proposals since the join only returned the active proposals.


I meant to say no longer displayed the NOT active proposals and ONLY shows the ACTIVE proposals.

Sorry again.



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-10-26 : 13:23:48
Your data design is what I have done. Or close enough and I'm going to read and reread it quite a few times for additional insight. That's a huge half of the puzzle. Very nice to get confirmation that what I've done isn't outrageous. Thanks.


But your request for an datagrid example I'll try to address briefly here. Really I'm requesting an example as well. Basically what I typed shows two datagrid rows in a textual representation of the graphic data grid. Number 1 being the key and A,B being the values. I guess it failed.

The datagrid is made up of rows and each row represents a current (latest) line items.
Each current line item may or may not have a previous version
Each previous version may or may not be exactly the same as the subsequent line item.

So the question becomes..
If the previous version(s) differ from the current version were you able to display the rolled up datagrid row formatted in a different color? Or will I be able to.
I have looked for a few days and haven't seen one.

ORANGE in the above example represents a current item that is in compliance with the previous verion (last) but has gone thru a change at some point in it's lifetime; in this case (oldest)
Item 1 value A(current) = Item 1 value A(last)
Item 1 value A(current) <> Item 1 value B(oldest)

RED in the above example represents a current item that is NOT in compliance with the previous verion (last) and
therefore has gone thru (or is going thru a change) in it's lifetime

Item 1 value A(current) <> Item 1 value B(last)
Item 1 value A(current) <> Item 1 value B(oldest)


If the previous versions (<- note it could be many) line item differs from the current line item did the unclicked (unexpanded or rolled up) datagrid row show a format change. I find this kind of challenging and boy you are right an example would help. That's kind of the reason I posted here. See the rolled up view needs to be "Directive" to where the user should focus their conflict resolution. I have succeeded in having that rolled up row showing a flag column like Changed!!! But I wasn't to pleased with that. So I guess that is why I was working on the offset "difference list" presentation as demonstrated with color in the very first post as a way to direct the actions of the user in the comparison of sets.
It's all getting very "griddy" at this point and while the functionality of drag and drops from searched list to grids to underlying datatables and updates to SQL is all working decent I'm thinking if I could bind the "difference" formatted current rows to a simpler set of controls I might be better off. I was looking at some Dessault systems representations of the same thing but I kind of need a hands on demo to see how the useability is.

So Since we both arrived at a conceptually similar versioning resolution I guess my puny interface skills are showing. Yep an example would definitely help. Got any?

I wish someone would start an Official XML Rant Thread.
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts
Posted - 2005-10-26 : 13:40:52
And a last try to show what the grid would look like
assuming three versions per row

and row with columns Item,value


Current version rolled up


+ 1, A
+ 2, XX
+ 3, LLL

with each row expanded

- 1, A
___1, A
___1, A

- 2, XX
___2, XX
___2, XY

- 3, LLL
___3, LLP
___3, LLP


Item 1 stayed consitent accross all version
Item 2 has a delta in it's value accross it's versions but NOT on the most recent one.
Item 3 has a delta in it's value accross it's version and a delta IS present in the most recent one.

That's pretty cool, I think I'm gonna have to figure those datagrid style paint hook ups which I was avoiding.



I wish someone would start an Official XML Rant Thread.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site