I need a push... apprehensive re. data entry interface 1

[etalent]

I've got a database with five parent tables, three of which you can write to via their relationships, two of which you can't write to via their relationships. Some of the child tables can't be written to either. I've setup the relationships in such a way, so as to maintain data integrity.

Here is the relationship graph:

New records have to be created on a monthly basis. The two tables [Wells] and [Owners] will rarely change, so all of the new records will be created in the three tables that join them, specifically, tables [Production], [Join], and [Checks].

What I need help with is making a user interface for entering data into the [Production], [Join], and [Checks] tables for when I need to create the new records monthly.

I'm really apprehensive that I'm going to break all I've done or corrupt my data. I've saved numerous backups, so there is no danger I'm going to lose anything forever. I just don't really know what I'm doing as far as making a way for entering new data without endangering all of my data. I do know that setting the relationships as I have is a good thing, but I need a push to continue.

I know how to make value lists and all of the other controls. I'm just scared to break this thing, now that I've sweat blood into it so much.

Here is the database itself:

Please help! Thanks.

Dave

 

[JeanW]

What is your process for creating new records ?
What is the data.

Where, why and how.

I can't find a 'real' data-inputscreen.

And I think this what you need. An input process written down as first step. The second step is to give the user the possibilty to input the data the way you want.

Also, your relationshipgraph is confusing like it is right now.
Close all the subscreens (click on the little square at the top right of every TO), that way you only keep the real related information.
Move Wells to the upper left corner, Production just below Wells and Years just below Production.
Join next to Wells, Gasprices and Taxrates below Join.
Move this way all your TO to the left.
This will give you nearly one horizontal overview of the relationships.
This is easier to read.

If you could give us a detail view at the inputdataprocess (what, where), we possibly could guide you into a scripted process.

 

[etalent]

JeanW,

Thanks for the reply. I am really relieved to get some help.

Now that I've thought through the whole system for the 500th time, I think I have a good idea for how to do data entry. Here it is... first some background...

The database, as it is now, has a test "batch" of checks that I have successfully created. Go to layout "Owners" and view as form. That is what I want to create with every month's batch of data entry.

Now go to layout "Join." It doesn't look like much, but notice the number of records (799). I think that this is where I should begin with data entry. Please allow me to explain...

Table [Join] accounts for the relationships that every owner has with every well. More specifically, every owner has some portion of an interest in every well. Put another way, one well might be owned by 20 different owners, and one owner might own interests in 20 different wells. I picked the number 20 at random just for example sake.

Back to the data entry issue... Let's say that you go to a data input screen. On this screen you pick a month from a drop down value list for the new month for which you have to enter data. Let's say you pick "May."

When you pick "May," a script runs to create a copy of the values from "April" as the new values for "May," because all of the values are mostly the same except for [Production].[McfId].

- - - - - - - - - - - - - - - - - - - - - - - - - - -

Regarding the relationship graph...

I imagine that my relationship graph may be confusing for you or anyone else not so familiar with this database. Please allow me to explain, and maybe you'll agree that the way I have the relationship graph arranged now is not so confusing after all. On the other hand, if you cannot help me unless I do rearrange it I will.

Of the five most important tables, which are [Wells], [Production], [Join], [Checks], and [Owners], the two tables [Wells] and [Owners] seldom change, so I put them on the "outside," furthest on the left and right sides of the graph.

I put the remaining three "most important" tables [Production], [Join], and [Checks] in the middle of the relationship graph because they join tables [Wells] and [Owners].

Now let me speak to the little "ancillary" tables underneath the main tables.

Under table [Production], tables [Years], [GasPrices], and [TaxRates] have one to many relationships with their parent table [Production]. These three child tables of [Production] cannot be written to via their relationships with [Productioun].

Under table [Join], child tables [Mcfs] and [Interests], likewise, have one to many relationships with their parent table [Join] and cannot be written to via their relationships with [Join].

Finally under [Owners], tables [Cities], [States], and [Zips], have one to many relationships with their parent table [Owners] and cannot be written to via their relationships with [Owners].

Again, in general I have the tables arranged as I do to imply that the tables [Wells] and [Owners] are pretty much set, that is, when new data is entered on a monthly basis, the data in tables [Wells] and [Owners] does not normally change. On the occasion where, say, an owner's contact information changed, then the appropriate data would be changed in table [Owners] or in the child tables of table [Owners].

As for occasions where data relevant to a well would have to be changed, data in table [Wells] would be changed.

If the data normally does not change in tables [Wells], [Owners], and the child tables of [Owners], yet data has to be entered on a monthly basis, then where does the data get entered, or, more exactly, the data of which tables gets changed?

Thus, the real data entry problem. I will try to be as exact as possible. When monthly data is entered, here are the tables, and the fields within them, that will change:

[Production]
.[ProductionId] A new record gets created each month.
.[WellId] A specific well is associated with each record. Because a new record is created every month, each well will occur many times. Thus the one to many relationship.
.[ProductionMonth] Autocreated upon data entry
.[Year] A specific year for each record
.[Gross] Calculated field
.[Tax] Calculated field
.[Net] Calculated field
.[NetInterest] Calculated field
.[GasPriceId] A specific gas price for each record
.[TaxRateId] A specific tax rate for each record
.[InterestTotal] Calculated field

[Join]
.[ProductionId] Each record in table [Production] contains unique information. Most wells will produce a unique quantity of gas each month, but some wells will product the same quantity of gas as other wells. Thus, the one to many relationship.
.[CheckId] Each owner owns interest in many wells. The production information for the many wells is contained on one check stub. Thus the many to one relationship.
.[McfId] Each well produces a certain amount of gas (MCF), or thousands of cubic feet. Again, most wells will produce a unique quantity of gas each month, but some wells will product the same quantity of gas as other wells. Thus, the many to one relationship.
.[InterestId] Each owner owns a specific interest in each well, which is the same in some instances and unique in other instances.

[Checks]
.[CheckId] A unique number for each check. Each owner gets one monthly check.
.[OwnerId] Again, each owner get one monthly check.
.[CheckDate] Autogenerated upon date of data entry
.[Amount] Calculated field
.[Text] Calculated field

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Getting back to your post... Now that I've provided the logic behind my design, I'll address the desired process.

The end goal is to generate checks. If you downloaded my database, go to layout "Owners" and view as form to see the 332 checks I have generated for each of the 332 owners.

So if generating checks is the ultimate process goal, then the intermediate goal is getting the right data in to the right fields without corrupting anything.

Let's take a step back...

It all starts with production. Every month, each well produces a certain quantity of gas, which is stored in [Production].[McfId].

Sometimes gas prices change. Sometimes, tax rates change, and the date is always new, but the only data that really changes every month is the MCF.

The data input screen should allow data entry in tables [Production], [Join], and [Checks].

The data input screen should allow data entry for table [Production] for the following fields:
[Production]
.[ProductionId] (autogenerate, hidden)
.[WellId] (portal)
.[ProductionMonth] (value list)
.[Year] (value list)
.[Gross] (cacluated, hidden)
.[Tax] (cacluated, hidden)
.[Net] (cacluated, hidden)
.[NetInterest] (cacluated, hidden)
.[GasPriceId] (value list)
.[TaxRateId] (value list)

Likewise, the data input screen should allow data entry for table [Join] for the following fields:
[Join]
.[ProductionId] (spawned from [Production].[ProductionId], hidden)
.CheckId (autogenerated, hidden)
.McfId (value list, very crucial)
.[InterestId] (extremely crucial, can make or break the whole system)
.[InterestType] (value list)

Last, the data input screen should allow data entry for table [Checks] for the following fields:
[Checks]
.[CheckId]
.[OwnerId]
.[CheckDate]
.[Amount]
.[Text]

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

As I started out at the first of the post, I think that the answer may best be found thus:

Have a data input screen with a month drop down control. If you pick, say, "May," a script runs to create a copy of the values from "April." A new set of 799 records gets created in [Join], a new set of 333 new records in [Checks], and a new set of 57 new records in [Production].

Then the main job would be to go through and set the values for [Production].[McfId] correctly, which would cause new calculated values and new checks to get created too.

What do you think?

 

[JeanW]

That's a lot....

Give me some time to see if I can find my way in all this.
I don't know who helped you building this, but at first sight it is the wrong way. It is focussed on relationships.
Your design is build to make relationships work and not to make data manipulation work. Hence your problem now to add data.

One point, what if you have to remove a Well or a Production are whatever. Not very dynamic.
It's based upon a sytem that is pushed into 'the reality'.
The basic for databases is to pull a process into a database.
This is the major flaw in your design, based upon a wrong approach.

The data in a join file has to be made through a portal. It seems to be impossible in your design. We have to find another way.

Without going deeper in detail and just having a quick reading (didn't look really yet at your database), focus on the fields who are not calculated or populated with a sort of mechanic.

Make a central layout where you put those fields, ready to be populated with new data.
Those will be fields from the table where you put your layout and several fields :fieldName) from related tables.

See which fields you need to make new records.
Try it out on a copy and see which records are not created.
Those are the ones who need more attention.

Again, it's a first approach. I have to 'study' your design first. And I don't even know if I will find a workaround, just to avoid to rebuild the application with the normalization rules as basis.

Will talk later.

HTH

 

[etalent]

Thanks again and again for your help. I built this database myself, with no help from anyone else really.

I have started over probably 100 or more times. I shudder to think of starting over again, but, of course, I will if necessary. I have all the data I need to create a sample batch of "checks," which is the end goal, so I can import data pretty painlessly once I have the tables and relationships redone according to an improved structure.

I don't mean to complicate. The problem is really very simple.

There are 57 wells and 332 owners. The owners share ownership (interests) in the wells, so, for example, one owner might own interests in 27 wells. Put another way, the ownership of one well might be shared between 250 owners. These numbers are just random.

The data entry comes in with the amount of gas that each of the wells produces (MCF, or thousands of cubic feet).

You have to create a check for each owner, each month. The check stubs have to account for all of the wells that each of the owners own interests in, and MCFs, tax rates, gas prices, etc.

My biggest and best idea is to base each month's new batch of checks on the previous month's data, because it's largely the same data, except for the MCF values (and the values of the resultant calculated fields). Once you get a batch of data for a month (by copying the data from the previous month), you just correct the MCFs. Then live happily ever after!

Sorry for all the writing. I really am trying to be brief, believe it or not! I just want to be sure to be clear.

Thank you yet again!
Dave

 

[JeanW]

Hi Dave,

no problem. The more information the better.

By looking over your process from different angles you came up with the real solution.

#1. What do you need ? A check.
#2. Which data ? 1. - 2. - 3. ......
#3. What is genratig the data ? Well, Rate, etc
#4. What do I need for that ? McF etc .....

and not, I have Owners, Wells and Mcf's and now I'm going to make checks....

It looks the same, but it isn't.

I suppose there is a way to send a file to you.
Your site is your nickname period com.
The forum rules here don't allow a mailaddress (plain written), so I think there will be a way to send you a file somehow to a cryptisch address....

 

[TStriker]

Yikes! I hadn't even sneaked a peek at this thread until a few moments ago. I don't have time to even read the whole post right now much less try to understand it but will try later tonight if I get a chance.

-Striker

 

[etalent]

JeanW, Striker,

I am heartened!

In the earliest posts I was still trying to articulate the thing. Maybe just skip down to the later posts.

JeanW, thanks for the relationship graph. I don't see the advantage of what you've done at first look. I'm getting pretty brain dead. I'm doing a bunch of other things at the same time, but the show must go on...

Thanks again guys!!!

 

[JeanW]

David,

I took a little closer look.
For the graph part, your relationshiplines doesn't clearly mark which field is related to which field. The lines are on top of eachother. Strech your graph to avoid this. It is hard to 'read' the graph when the boxes are fully open, showing more (not related fields) then needed to understand and see the info flow.

For a part I agree with the reaction of Striker. I recognize the signature from another FM user.

As far as I can see now (and correct me if I'm wrong):
The basic and final goal is checks.
Each check should show for each owner:
list of wells in which he owns an interest
the Mcf of those wells
the total Mcf of the wells for the given owner
those data for each given month
each given month for each owner a check with the above data

If this is true, you have (my idea) a major design flaw.
You have to turn the design upside down and have to start with what a check is, which information you need for each check and make it possible to input the data each month.
Those are the requirements.

As it is now, you will have a hard time to add a well, an owner and a McF.
And I don't talk about what will happen when you delete/remove an owner or well.

It is also not very clear now how you manage the possible change for the interest of each owner in a well.
I suppose when a well has 100 %, changing the interest from 1 owner, will generate a change for another one.
How do you tackle this ¿


The value, interest, money etc is used as link.
Whenever you change one of those values, the places where you used those values will change also, which will break the consistency.
F.i. in january you have given values for the above.
You print and send out the checks, no problem.
In February some values change, you make the changes and the values on checks, printed in January, will also change !!!!
Here's your design flaw.
The data has to be add in each table separatly.
Maybe I can come up with something using a timebasedkey field, but then I have to know exactly how you add now the data for each check.
What is your process to do it.¿¿

HTH

 

[eta1ent]

JeanW, TStriker,

My Tek-Tips account broke when I inclued an email address in a post, so I opened another acount with another email address.

I'm stuck, so far as how you can get a file to me. Can you FTP something to somewhere and then provide me a link to it?

-Dave (now "eta1ent" with a number "1" in place of the letter "l," formerly "etalent")

 

[JeanW]

Well...I told you so, email addresses are not allowed on this forum...

My site is still not ready for ftp, our provider changed the servers and our site was gone....simple.

Go to our new one at

http://www.paginasprodigy.com/pesmerida

, look for a contactaddress.

I still have to go more in dept in your file and untill now I don't have a solution that will work in every circumstance.....

 

[eta1ent]

JeanW,

¡Ay caray! ¿Como es que puede tener una escuela en la Yucatan? Mi esposa, de 19 años de matrimonio, es del norte (Monterrey, NL), pero todo de eso es otra cosa de que hablar...

Le agradezco muchisisisisimo por todo su ajuda.

-Davi

 

[JeanW]

David,

do you have already answers to my questions in my previous post ¿

...and yes, it's not only a language in Merida, database and Project Management is the second part....

 

[eta1ent]

JeanW,

I answered all of your questions with much care in the post that got eaten, because I put that email address in. I'll do over.

It's 12:00 midnight here now, and I've been hammering out resumes, studying for this application that I'm going to have to develop this weekend in C with LabWindows/CVI, and basically abusing myself all day. Forgive me, but I've got to hang it up for the night.

Hasta mañana. Lo siento mucho, pero mis cerbros estan derretiendo.

-Dahveed

Que cuide por el hurrican... vivimos por Miami hasta el verano pasado.

 

[eta1ent]

JeanW, TStriker,

I did feel really bad that I haven't figured out everything easier or faster, but now that I see that my little project is somewhat challenging for you guys, I think I get a little perspective.

I think what I'll do is take a step back and rethink yet again, for the 500th time, what I'm doing. True, I was putting the relationships first, not data entry, so I'll try rethinking everthing with data entry first.

I really have learned so much about Filemaker with this experience, pero como el Español (y las latinas), lo mas que aprendo, lo mas veo que aprender...

I wanted to put some kind of post up here, so you'd know I'm still in this fight. I do have 50 other things I'm doing all at the same time...

Thanks for all your help! ¡Voy siguir visitando su web site! ¡Adelante! Acuerde que estoy disponible para ayudarles si puedo. Soy escritor technico con una diploma (BA) en "technical writing" y 18 años de experiencia con unas de las companias mas grandes en los EU (Lockheed Martin, GE Aircraft Engines, Bell Helicopter, Hamilton Sundstrand, Boeing, Honeywell, Carrier, Parker Aerospace, Crane Lear Romec, Raytheon, Rolls Royce, Pratt & Whitney, United Avionics, mas). Falo o Português y tenho um amigo que é um traductor no Brasil. ¡Intercambiamos favores!

-Davi

 

[JeanW]

Davi,

those are one of the drawbacks of FileMaker.
It's so easy to start with that most people just start to click around untill 'it works'.
Although this is not a bad way to learn. Not that bad to put a addressfile together.

You took a big one to start, and the outcome is not that bad after all. Only your application came to a nearly halt.

Nobody told you to start with the requirements (what do I need, which data, which information, where is it, how do I reach it etc)

I would keep the file like it is now and try to make a check.
You are the only one who knows what needed.
Build it up from there and use scripts to do your steps.

I'm willing to spend a few more time to see if I can implement something to help you on your way.
Meanwhile, think your process over from the check angle, and start building from there.

HTH

 

[eta1ent]

JeanW,

I assume you've got my database.

Go to the "Owners" layout. Browse as a form. There is one "batch" of checks. That, in itself, was no small accomplishment for me.

The problem is in getting new data into the system efficiently for the next month's checks without corrupting existing data... Thus, my idea of spawning a new "batch," based on an existing batch. The "batch" could get spawned by selecting a month, say, from a drop down. Just an idea.

THANKS!
-Davi

 

[JeanW]

That what I said, you did not a bad job..

Only, there is no way to add records without blowing your system apart.

It's not only adding an Owner or a Well.

Take a possible change in the interest or prize.
The change will affect all your existing records, blowing away your history.
What, if after a change in those records, there is a need to print again, or to check (control) a record from several months ago. The values will be wrong, reflecting the actual values and not the history values. Those will be gone...

Your ID field in every table is not bad, but is says nothing about the attach value.
Creating a record and then...you see an ID value... representing what ? there is no way to find it out, unless you navigate to that record in that table to see...

You can use relationships and related fields, but for your application it's nearly a must to work with lookups to retieve values from other tables.
Work with GTRR (Go to related record) from your check table.

Your basic idea is good, your database also, keep it. Make a backup. And start in a copy with your checks.
Make there a new record and see what you need.
get the values from the tables, while you stay in the check table.
This way you will understand which relationship you need, which field you need and how to do it.

HTH

 

[eta1ent]

Jean,

Wow... Mis cerebros estan derretiendo. Thanks.

I do love a challenge.

-Dave