Unable to display multiple rows

[Rob Kellow]

Hello,

I have a SQL database for task tracking. The Activity table has columns for
the various tasks and who worked on it. I have an InfoPath form to do the
entry and published it to a SharePoint Team site. I had everything working
using a Repeating Table and the user could just enter a new row for each task
they were reporting on but then there was a request to show a certain kind of
task separately from the other tasks. There are five of these "special"
tasks and the new requirement is to show them separate and always on the form
along with the current Repeating Table of other tasks. I can't figure out
anyway to do this with current database schema.

Does anyone have any thoughts on how this could be accomplished. Thanks in
advance for any help.

Regards,
Rob

 

[mag31]

Hi Rob,

You could try filtering the data by:

1. Right Clicking on the repeating table
2. Go to Repeating Table Properties.../Display/Filter Data...
3. Add...

You could then just bind a new repeating table in your form to the activity
table and get it to filter out the special tasks.

Best Regards,
Mark Grant
Cambridge Convergence Limited

 

[Rob Kellow]

Hi Mark,

Thanks for the response. Unfortunately, it didn't work out for me. Part of
it might be that I left out a few key details and part of it might be that
I'm still doing something wrong. Here are the key details: 1) The Activity
table is going to be updated by what the user enters, so there is nothing to
filter out when the user first comes into the form. Any row in the repeating
table the user fills out will be a new row in the Activity table. 2) This is
fine for the regular activities because any given user will only work on one
or possibly two of the regular activities. But any given user will work on
most if not all of the "special" activities and this is why the requirement
is now to show these special activities on the form as opposed to selecting a
regular activity from a drop down in the repeating table. 3) The "Special"
activites don't need to repeat (in fact they shouldn't) there is only five of
them and they cross all boundaries of the other regular activites. And the
new requirement is to show all five of them on the form.

That being said I tried what you said anyway with the filter and what
happened was a bit strange. I created a repeating table that filtered on the
special activites and another repeating table that filtered on everything but
the special activities and what happened was when I added a row to the
special activities, a new row automatically was added to the regular
activities table as well. When I entered a value in the new row in the
special activites table the value also showed up in the regular activites row
that was added as well, strange?

Any further help or thoughts would be greatly appreciated.

Thanks and Regards,
Rob

 

[mag31]

Hi Rob,

Can you clarify your data structure for me? The data structure has a massive
impact on the presentation of the form.

My initial thought was that your data structure should look like:

activity
|
--- activity_type
- activity_name
- type_flag

where the type_flag tells you whether the activity is regular or special.

Now, I get the impression your data structure should be:

activity
|
--- special_type
- name (one of your five names)
|
--- regular_type
- name

You then have a special_activity table, which has a list of the 5 special
activities and a regular_activity table, which has all the regular
activities. Both of these data structures assume that you store the same data
for each activity - Is this correct?

Mark

 

[Rob Kellow]

Hi Mark,

Thanks again for your help. You're right about the importance of the data
structure and I've been trying to figure out if there is a better design but
so far haven't been able to come with anything. I wish I could attach a
diagram but since I can't, your first one is the one have have:

Activity (Table)
|
|_ UserID
|_ ActivityFlag
|_ ActivityDate

 

[mag31]

Hi Rob,

The special activities do put a bit of a spanner in the works!!! I think you
are going to need to change your schema.

A really simple but not very clever solution would be to split out 5
separate tables for the 5 special activities. Another idea which may be a
little less onerous (you are going to have to play with this one):

Split out special and regular activities as two tables.

When someone submits a special task it goes into this table.

special_user_activities
|
-- special_user_id
-- special_id

When someone submits a regular task it goes into this table.

regular_user_activities
|
-- regular_user_id

We link the special_id to the special_activity table, which lists the 5
special tasks

special_activity
|
-- special_id
-- activity_type

Finally, we need an activities table:

activities
|
-- activities_id
-- special_user_activity_id
-- regular_user_activity_id

In you Infopath form we create a data structure with activities,
special_user_activities, and regular_user_activities. This can be used to
create a control stucture with repeating sections for the
special_user_activities and regular_user_activities. Ok, now to display your
5 special activities create a secondary data source for the
special_activities table. Drag and drop the special_activities data source
into the special_user_activities repeating section control. This will display
all five activities when you run the form.

Good Luck,
Mark

 

[Rob Kellow]

Hi Mark,

I thought of the first suggestion you mention but that kind of leads to the
second one since along with breaking out the 5 special activities you also
need to break out the regular ones, yes? I thought that if I have the 5
special activities broken out into their own tables that would be a
one-to-one relationship with the Activity table but I still want a one to
many relationship with the regular activitities. Correct me if I'm wrong but
I think that would mean removing the regular activities from the Activity
table and having them in their own separate table as well.

The question I have about you second suggestion is what do I make the "Main"
connection. It appears to me that one of the major problems I'm having with
continuing to do this project in InfoPath is that I can only submit (write)
data to one data connection. If I were doing this in something else I could
read from and write to as many tables as I want but by having the constraint
of having everything in one connection for submitting purposes seems to be
causing a lot of problems. You said about making the special activities a
secondary data source but then I cannot submit data to it, correct?

I started looking into writing a web service for this since it appears as
though by using a web service I could submit data to secondary data sources
as well, any thoughts? The whole thing is getting a bit messy and taking a
lot more time than I had hoped. I apprecate your help and thoughts. If you
have any ideas about using a web service or if any of my assumptions are
wrong please let me know.

Thanks again for all your help,
Rob