?
Solved

Effectively a sort of Sub-Summary Serial# applicable to IWP

Posted on 2011-02-28
12
Medium Priority
?
426 Views
Last Modified: 2012-05-11
In my database I have a table named "Property" which contains a field named "PropID" which is a autoenter serial number which will apply a unique identifier to each specific record.

Each record contains the field "Lands" which is not a unique field among the remaining records.  I am interested in finding a way to create a sort of "sub-summary" serial id according to the groupings of "Lands" records.

For example, if I have the following records:

Serial#             Description              Sub-Summary Serial#
PropID01          LandsA                    LandsA1
PropID02          LandsA                    LandsA2
PropID03          LandsA                    LandsA3
PropID04          LandsA                    LandsA4
PropID05          LandsB                    LandsB1
PropID06          LandsB                    LandsB2
PropID07          LandsB                    LandsB3
PropID08          LandsC                    LandsC1
PropID09          LandsD                    LandsD1
PropID10          LandsD                    LandsD2

There are two major issues.  (1) The process/calc must be applicable to a database that is IWP and (2) the sub-summary serial number must be static b/c there is a possibility that a record's status will require that it be deleted, therfore, simply using a count function will not solve my issue.

I thank you in advance for all of your help and will reward points/grades quickly for any solution I receive that solves my problem.
0
Comment
Question by:johnsodolak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 12

Expert Comment

by:North2Alaska
ID: 35003205
I'm not sure I understand the need for the Sub-Summary Serial#.  Can this not be done with a couple of TOs and a relationship?
0
 

Author Comment

by:johnsodolak
ID: 35003495
??? TOs and a relationship???

What I am trying to do is effectively give each record a unique identifier per its group.   That grouping being defined by the "Lands/Description" field, as illustrated in the example.

I am not sure that "sub-summary" is the correct terminology.  I simply used the term b/c in a report I can group these records according to adding a sub-summary part.  Using that thought process, say I ran a sub-summary report for the above example, each of those groups would be defined.  Now I would want each of those records in the group to have an identifier or key that would reflect that it is part of that group (i.e. LandA#), but that will still make that record unique (i.e. LandA1, LandA2, etc.).
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 35003687
TO = Table Occurrence.  Relationship is what joins two TOs together.  

Here is a very simple example where you have a relationship from Property to itself using the Description field.  As a result you can see all the serial numbers that are related to that description.  This seems to be a better way, as the serial number is the KEY to that record without having to produce some other identifier.

Take a look and see if this helps or if I've totally missed the whole point.  :-)
Property.fp7
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:johnsodolak
ID: 35003744
I apologize!  I did understand what a relationship what, I was simply not sure what "TO" was and how it related to "relationship."  I do know what a table occurrence is also.

You are correct that I do need to have a unique identifier/key for every record, but, for reporting/output purposes and "spatial" analysis I need to have a second identifier.

Your .fp7 example is right along the lines of what I already have.  The only difference is that I need a third field that will restart/reset the numbering system w/in each "group" (i.e. LandA, LandB, LandC).  Essentially resetting my numbering system for each group.
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 35005869
OK, and you are thinking that if I have LandA3 and LandA4 they will never change.  If I were to delete LandA3, LandA4 would still remain and a new item in that same group would be LandA5.  Is this correct?
0
 

Author Comment

by:johnsodolak
ID: 35007317
Yes North2Alaska, that is absolutely correct!
0
 
LVL 9

Accepted Solution

by:
jvaldes earned 1000 total points
ID: 35009308
Add a global field to the lands database Called LastLands and store the last use of LANDS letter there.
Create a LandsDetail database where you would store all the LANDS specific data. Every time you want to create a new LANDS look in the LastLands field and increment the field and carry the value for its use. Create your entries in the LandsDetail as needed. In the LandsDetail Data table create a global LandsNumber and do the same as above.

Though I think I have answered your question I suspect you are creating an approach that has already been built into database structures, but you may not know how to ask the question. Can you provide a description of your database objective (Big view) I have a feeling there is an easier way.
0
 

Author Comment

by:johnsodolak
ID: 35039275
Thanks jvaldes, I will try this and get back w/ you!
0
 
LVL 12

Assisted Solution

by:North2Alaska
North2Alaska earned 1000 total points
ID: 35057516
Here is a rough solution.  It works as you described.  One thing to work out is if you change the description, the sub description does not change.  What would need to be done is capture the description when you first come to the record and then when a value is entered, check to see if was changed.  If so, re-run the script to set the new value.
Property.fp7
0
 
LVL 9

Expert Comment

by:jvaldes
ID: 35338236
ok with me
0
 
LVL 8

Expert Comment

by:Tocacar
ID: 35364711
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month8 days, 12 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question