Selection Help
-
I need some help. I need to be able to select a "Time Log Reason" if all the possible combinations are not used. If all the possible combinations have been used, then it should no longer be available. Here's an example:
Time Log Reason = Travel
Type = Installation and Relocation
ServiceZone = LB and SD
Possible Combinations:
1) Travel, Installation, LB
2) Travel, Installation, SD
3) Travel, Relocation, LB
4) Travel, Relocation, SD
So, is it possible to display "Travel" if combination #4 was not used yet? But, if all 4 combinations were used, then Travel should no longer be available to select. I am trying to use this in a program to have a list of values to select from.
Here are my tables:
CREATE TABLE [dbo].[tblPartMapping]([PartMappingKeyID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [varchar](75) NOT NULL,
[TimeLogReason] [varchar](50) NOT NULL,
[Type] [varchar](50) NOT NULL,
[ServiceZone] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblServiceZonesAvailable]([ServiceZone] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblTypesAvailable]([Type] [varchar](50) NOT NULL
) ON [PRIMARY]
The table "tblPartMapping" is the table that holds all the combinations that have been already used. The table "tblServiceZonesAvailable" is the master table that contains the unique entries of LB and SD. The table "tblTypesAvailable" is the master table that contains the unique entries of Installation and Relocation. So, is this possible? I would greatly appreciate some help, as I've been stuck for quite some time on this. Please use any combination of views, functions, procedures, etc... I just need it to work. 🙂 Thank you in advance!
-Chris
-
I suspect you are going to make the requirements more complicated, but this is a start. We simply create a table to hold all four possible combinations and then test to see if they are all found by a join to your mapping table. I did this with a table, because you might have a different number of combinations for some other time log reason, or there might be combinations that are irrelevant. Use of the combo test table gives you perfect control.
Let me know if you have any questions, or if you can take it from here.
CREATE TABLE #tblPartMapping(
PartMappingKeyID int IDENTITY(1,1) NOT NULL,
ItemID varchar(75) NOT NULL,
TimeLogReason varchar(50) NOT NULL,
[Type] varchar(50) NOT NULL,
ServiceZone varchar(50) NOT NULL
)
insert into #tblPartMapping
select 1,'Travel', 'Installation', 'LB'
union all select 1,'Travel', 'Installation', 'SD'
union all select 1,'Travel', 'Relocation', 'LB'
-- union all select 1,'Travel', 'Relocation', 'SD' -- comment/uncomment to test
CREATE TABLE #comboTest(
ComboID int identity(1,1) NOT NULL,
TimeLogReason varchar(50) NOT NULL,
[Type] varchar(50) NOT NULL,
ServiceZone varchar(50) NOT NULL,
primary key (timelogreason,[type],serviceZone)
)
insert into #comboTest
select 'Travel', 'Installation', 'LB'
union all select 'Travel', 'Installation', 'SD'
union all select 'Travel', 'Relocation', 'LB'
union all select 'Travel', 'Relocation', 'SD'
----------------------------------------------
declare @itemid int
declare @reason varchar(50)
set @itemid = 1
set @reason = 'Travel'
select case when
(select count(*) from #combotest where TimeLogReason = @reason) =
(
select count(distinct comboID)
from #tblPartMapping p
join #comboTest c on c.TimeLogReason = p.TimeLogReason
and c.[type] = p.[type]
and c.ServiceZone = p.serviceZone
where p.itemID = @itemID
and p.TimeLogReason = @reason
)
then '' else @reason end as TimeLogReason
drop table #tblPartMapping
drop table #combotest
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Bob Hovious (8/21/2009)
I suspect you are going to make the requirements more complicated, but this is a start.Thanks for the reply Bob! And, you're right. I'm going to make the requirements more complicated. 🙂
Is there a way to make the comboTest table dynamic? Here's why: I have another section in my program where the person selects what Types and Service Zones are available to map. So, LB and SD might not even be available in the section where I am mapping my Time Log Reasons. Or, for that matter, Installation and Relocation might not be there either. So the various different combinations will be dynamic.
It kind of has to be this way. Because in my main program, the person can then select what Service Zones (that have been already mapped) or what Types (that have already been mapped) to use during the selection of what time logs to convert to parts automatically.
So, did I just make it more complicated because of that? 🙂 Let me know if you need any other information. Thanks!
-Chris
-
Lo, I am a prophet. 😎
Of course you can populate a combo table with whatever you want. If you are going to be populating [TimeLogReason], [Type], and [ServiceZone] every time, it should be a piece of cake. All you have to do is provide lists of values and the following code will build a combo-test table of all the combinations.
declare @TimeLogReasons table (reason varchar(50))
declare @Types table ([type] varchar(50))
declare @serviceZones table (zone varchar(50))
----------------------------------------------------------------------------
--- have your application populate these tables with selected values
----------------------------------------------------------------------------
insert into @timeLogReasons
select 'Travel' union all select 'Other'
insert into @types
select 'Installation' union all select 'Relocation'
insert into @serviceZones
select 'LB' union all select 'SD'
----------------------------------------------------------------------------
--- builds list of all possible combinations from reasons/types/zones
----------------------------------------------------------------------------
select Identity(int,1,1) as comboID, reason, [type], zone
into #combotest
from @TimeLogReasons
cross join @Types
cross join @ServiceZones
create unique clustered index #pk_combotest on #combotest (reason,[type],zone)
select * from #combotest
drop table #combotest
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Ok Bob.. I thank you for your help so far, but I am still having a problem. See if you can help me some more. 🙂 I think it is going to be more complicated. At least in my mind.
Instead of using temporary tables, I have just used a regular table in the mean time for my ComboTest
CREATE TABLE [dbo].[ComboTest]([ComboID] [int] IDENTITY(1,1) NOT NULL,
[LogReason] [varchar](50) NOT NULL,
[SOType] [varchar](50) NOT NULL,
[ServiceZone] [varchar](50) NOT NULL,
[RepNumber] [int] NOT NULL
) ON [PRIMARY]
And here are the records in ComboTest
ComboID,LogReasonSOType,ServiceZone,RepNumber
1,Install,Installation,LB,2
2,Install,Installation,SD,2
3,Install,Relocation,LB,2
4,Install,Relocation,SD,2
5,Travel,Installation,LB,2
6,Travel,Installation,SD,2
7,Travel,Relocation,LB,2
8,Travel,Relocation,SD,2
Here is my PartMapping table:
CREATE TABLE [dbo].[tblPartMapping]([PartMappingKeyID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [varchar](75) NOT NULL,
[LogReason] [varchar](50) NOT NULL,
[SOType] [varchar](50) NOT NULL,
[ServiceZone] [varchar](50) NOT NULL,
[RepNumber] [int] NOT NULL
) ON [PRIMARY]
And the records in my Part Mapping table:
PartMappingKeyID,ItemID,LogReason,SOType,ServiceZone,RepNumber
1,TRAVEL,Travel,Installation,LB,2
2,TRAVEL,Travel,Installation,SD,2
3,INSTALL,Install,Installation,LB,2
Now.. Here's the problem. When I select "TRAVEL" as my Item, I don't want things to show up that have already been mapped to this part, and only what is still available. In my example, the following should show up:
1) SO Type of Relocation, because it hasn't been mapped to this part yet, and there are still combinations available.
2) Time Log Install, because it hasn't been mapped to this part yet, and there are still combinations available.
Now, if I select "INSTALL" as my item, the following should show up:
1) Time Log Travel, because it hasn't been mapped to this part yet, and there are still combinations available.
2) SO Type Relocation, because it hasn't been mapped to this part yet, and there are still combinations available.
3) Service Zone SD, because it hasn't been mapped to this part yet, and there are still combinations available.
Now, if I select a 3rd part, "PROJECT" as my item, the following should show up:
1) Time Logs Travel and Install, because there are no mappings for this part, and there are still combinations available.
2) SO Types Install and Relocation, because there are no mappings for this part, and there are still combinations available.
3) Service Zones LB and SD, because there are no mappings for this part, and there are still combinations available.
The scenario for "PROJECT" would be different, only if all the possible combinations were already taken. For example, if all the possible combinations for the Log Reason Travel were already taken, then it would not show up as an option, because every combination is already accounted for.
I hope you can understand what I'm trying to get at, and what needs to happen. You have gotten me this far, but I am still stumped. Maybe my current setup is not efficient enough to what I need to do. So, if there is another way of getting at what I need to do, that would be great too. Thanks again!!
-Chris
-
Chris, I'm looking at your sample data right now. What would really help me is for you to give me sample inputs and outputs. Draw me a picture instead of saying it in words, please. For outputs tell me what you want to see, not what you are already seeing.
Edited to say:
P.S. Yes more complicated... but I think I see what you are driving towards, even though the only thing I know about your application is that it has dropdowns. Your example seems a little simplistic, in that the values available in each dropdown would seem to depend on what has already been selected in other dropdowns. You might be better served to send your application the set of combinations which are still unmapped to a given part and test the dropdown selections against them.
I will pose some hypotheticals in subsequent posts. I am also posting up scripts so anyone else can see the data. In my scripts I am shortening the itemID values so that I don't confuse them with the LogReason values.
if object_id(N'tempdb..#combotest') is not null drop table #combotest
;with comboTest (ComboID,LogReason,SOType,ServiceZone,RepNumber) as (
select 1,'Install','Installation','LB',2 union all
select 2,'Install','Installation','SD',2 union all
select 3,'Install','Relocation','LB',2 union all
select 4,'Install','Relocation','SD',2 union all
select 5,'Travel','Installation','LB',2 union all
select 6,'Travel','Installation','SD',2 union all
select 7,'Travel','Relocation','LB',2 union all
select 8,'Travel','Relocation','SD',2
)
select *
into #comboTest
from comboTest
select * from #combotest
if object_id(N'tempdb..#partMapping') is not null drop table #partMapping
;with partMapping (PartMappingKeyID,ItemID,LogReason,SOType,ServiceZone,RepNumber) as (
select 1,'TR','Travel','Installation','LB',2 union all
select 2,'TR','Travel','Installation','SD',2 union all
select 3,'IN','Install','Installation','LB',2
)
select *
into #partMapping
from partMapping
select * from #partMapping
--- everything above is simply setup of sample data
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
TRAVEL (ItemID = 'TR')
--------
1) SO Type of Relocation, because it hasn't been mapped to this part yet, and there are still combinations available.
2) Time Log Install, because it hasn't been mapped to this part yet, and there are still combinations available.
What about Service Zone? Shows nothing because both SD and LB have been mapped for TRAVEL?
or shows SD, because SD hasn't been mapped for INSTALL?
What results if a row like this exists in the partMapping Table?
4 | IN | INSTALL | RELOCATION | SD | 2
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Bob,
Thanks again for taking a look at this. Maybe some screen shots will help you. Here is what I am looking for. I have used TR for Travel part, IN for Install part, and PRJ for Project part. These are screen shots of the scenarios mentioned above, and also in your latest example.
Here is what the scenario for "TR" looks like
Here is what the scenario for "IN" looks like
Here is what the scenario for "PRJ" looks like
Now, if all the possible scenarios for the time log "Travel" were already taken, then "PRJ" would look like this
If "IN" was selected, and all the "Travel" combinations were already taken, then the Time Log Available selection would look like above also for that part. The remaining options would still remain the same as its screen shot, because they have already been mapped to this part. If I would have selected the SO Type of "Relocation" for the "TR" part, then all of the "Travel" combinations would have been chosen.
The program will only allow you to enter each part of the combination one at a time. So, after I select my time log reason, it will then move to the SO Type, and query all the available SO Types. After I select the SO Type, it will then move to the Service Zone, and query all the available Service Zones. After I select the Service Zone, it will then move to the Rep, and query all the available Reps. If the parts already has some mappings, it will query all available selections at once.
Also, I hope with the screen shots, it will have answered your last question. Let me know if this helps clarifies things for you, or what. 🙂 Thanks again!
-Chris
-
Bob,
Any thoughts on this?? Thanks..
-Chris
-
Arrrg!!!
Chris, I totally lost track of this. My sincere apologies. I will look hard at it tonight.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
That's ok, Bob... I'm just glad that you're helping me out at all. 🙂
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply