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

[etalentweb]

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

[Søren Dyhr]

created in the three tables that join them

You have in my huble opinion squeezed in a structure of considerable measures in, where I would expect one single join table, which as a consequence makes it tough to deal with. Perhaps you should tell us about the reasoning behind!

Could it be that you wish to have 3 join tables between, but know of no other way to avoid circular referencing, than put them on a string like pearls?

I think I would make 3 TOG's to deal with each aspect - but I'm still uncertain what made you make the graph the way you did. What is really strange is that no TO exists in more than once!

--sd

[etalentweb]

Thanks for the reply. I'm really relieved to get your help! I've structured the relationships as I have because it is THE ONLY WAY that would (1) enable me to provide layouts with certain portals that I have to have and (2) prevent many to many relationships.

I've restructured the tables and relationships a jillion times. Believe me. I'm sure that the way I've got the tables setup now is the only way. I shudder to think of touching the relationships. I will if I have to though. I defer to your recommendation! In other words, HELP!!

I think I've figured out a way to do the data entry. Please allow me to explain...

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).

A new month's batch of data (based on the previous month), could be created by a script that gets run when you select a month from a drop down. 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!

To do what I'm suggesting would necessitate creating new records in tables [Production], [Join], and [Checks], which are the three "inside" tables that join the two "outside." All of this would have to be scripted I guess.

THANK YOU!!!

Dave

[Søren Dyhr]

prevent many to many relationships.

Why is this important, and how well do you think you have avoided it?? When say that a well has many owners ...this is in my humble opinion a tough many to many relation to ignore???

To me is the Production table the join table in the relationship, where each owner has his share of the production, and is likely to recieve a check ...but to establish the size or the amount the owner is likely to recieve is a functional dependency not something requirering it's own table...

--sd

[comment]

Reminds me of this .

[LelandLong]

I've spent a lot of this afternoon pondering over your issue and looking thru your file; it's been a good challenge for the day.

In trying to re-create your concept from scratch I can see how you've ended up the way you have. Just when I thought I could cut down on tables I came up with different issues.

Thought I would put "out on the table" the conclusions I've had up till now.

1) Can the [Join] & [Checks] tables be combined? They seem to be tied together in the analysis

2) MCF needs to be part of [Production] since it is an attribute of the [Production] entity (every month/year that well produced a certain MCF

3) Your keeping records for every month right? No reason to have a [Year] table; it's an attribute of the [Production] entity right along with "Month". Same thing for "Gas price" and "Tax".

4) every month you can run a script that creates new record entries for [Production] data for each well, filling in the month/year for you. You fill in the appropriate MCF for each well. Once all Production data is entered you can run a 2nd script that would create all the checks automatically (1 for each owner I presume)

Have I missed anything? Does this help clear up anything?

Looking forward to the replies :P

[etalentweb]

Thank you for the reply! I'll respond to your questions one by one...

1) Can the [Join] & [Checks] tables be combined? They seem to be tied together in the analysis

I tried that among many other structures, but assumed that I could not do it. Here is why: Tables [Join] and [Checks] can't be combined, because doing so would cause there to be one check for each production record. In reality one check (stub) has many production records on it. Again, everything starts with the amount of gas that a well produces, specifically, the value of [Mcfs].[Mcf] associated with [Join].[McfId].

If you could join tables [Join] and [Checks], and yet still have many production records on each one check, OK.

2) MCF needs to be part of [Production] since it is an attribute of the [Production] entity (every month/year that well produced a certain MCF.

One of the structures I tried was as you suggest, with MCF as a part of [Production], but I determined that such a structure would not work because I have to provide a layout (somewhere) with a portal made up of fields [ProductionMonth], [WellNumber], [WellName], [Mcf], [Tax], and [Net]. After much trial and error, I determined that the only way I could provide such a layout was to structure the tables as I have. Again, if you can put [Mcf] under [Production] and still have the portal I described that works right, please do!

3) Your keeping records for every month right? No reason to have a [Year] table; it's an attribute of the [Production] entity right along with "Month". Same thing for "Gas price" and "Tax".

OK. I guess you're right. I can't think of a good reason to have [Year] as a separate table.

4) every month you can run a script that creates new record entries for [Production] data for each well, filling in the month/year for you. You fill in the appropriate MCF for each well. Once all Production data is entered you can run a 2nd script that would create all the checks automatically (1 for each owner I presume)

That sounds PERFECT, my friend!

Thank you, thank you, thank you, thank you, thank you, thank you...

[Søren Dyhr]

Reminds me of this .

Yes it must be a derivate of that solution, but some weird access conventions must have found their way into the solution, since a lot of you suggestions have evaporated, if ever introduced?

--sd

[etalentweb]

LelandLong, Søren Dyhr,

Thanks for all your help so far. I'm overwhelmed, but I think I have some perspective, now that I see how "not-simple" my little project really is apparently.

I think I'm going to take a step back and start over yet again, for the 700th time, but this time with planning for data entry early on.

This is actually the first thing I've done with Filemaker, and I am working on about at least 20 other things at the same time as this.

I wanted to post something here so you guys wouldn't think I've died off. True, I'm overwhelmed, but it's true too that I've got to get this thing done, and ASAP!

I really have learned so much! I'll be getting back into this all over again.

Oh... I did post earlier to this board. That's why you see earlier post of this same project too.

Thanks yet again!

-Dave