Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Material db design question


This topic is 8290 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm not really sure if this is the right forum, but I'm not even sure which one to put it in, I'm so confused! (If not, please tell me; a hint on where TO post it would be helpful. If it's a case of RTFM, let me know which part of the manual I should go over, or if there's another printed resource I should look into.)

BACKGROUND

I have a series of FileMaker 5.0 databases--hosted by FileMaker Server 5.0--used in conjunction with a third party product that uses FileMaker as its data storage and report generator. I've built upon that to also provide job/project tracking. So I've got the IntelliTrack db for all my labor & time data storage, and report running; the JobTrack db for all the individual job & project information, including client info and materials' costs.

Now I want to start splitting off some of the jobtrack db's functions (yes, I know, bad way to do it, but I had a deadline) off to smaller, more specialized db's--like for client contact info and materials--using relationships. My problem/question is with the materials db I'm creating:

I've assigned a Materials Invoice # (MatInv#) as a key to a record, and the user can input ONE item of a materials list, referencing the Job# (that is, the user inputs the Job#, which kicks off a lookup to fill in the job info, an auto date/time stamp, then the user fills in the material's description and cost).

The ONE item per MatInv# is the problem; ie, some jobs might have a whole list of materials, and I don't want the users to have to fill in, say, 10 different invoices (each with different MatInv#'s) for that same Job#. What I want is for the user to be able to add another list item, as needed, onto that MatInv#.

I'm stuck in the idea of having ADD and REMOVE buttons next to the Materials Description (MatDesc), which takes the user to a similar layout as the first, but with two MatDesc fields (MatDesc1, MatDesc2, etc.), or to the previous layout in the REMOVE instance, with a clearing out of the field added. But since I might have to do up to 10 or 15 fields and layouts, I'm not too keen on that approach (I'm not adverse to it, but I want to keep things as simple and as elegant as possible; yes, I am a Mac user).

I tried repeating fields, but could only get the first instance of the first record to show up on a report, and I'm seeing on this and other FMP lists that repeating fields aren't a good idea to use anyway. There's GOT to be a better way, but what?!?!

Thanx in advance,

dan

Link to comment
Share on other sites

If I understand what you want to do, you need one more file. You need a file where one record is a Material Invoice (by number) and a related transactions file (one to many with the Material Invoice) where a record is one line item on a Material Invoice. Please DON'T use repeating fields or multiple fields for the line items!

Your stucture might look like

Jobs --one-to-many--> Material Invoices --one-to-many--> Material Transactions

-bd

Link to comment
Share on other sites

LiveOak:

I think I understand the structure you lined out; thanks! But how would I do the add/remove items in the Transactions file? I'd like to avoid setting up 15 different layouts (exaggeration, but still...) if I can.

Could/Should I use a portal in the Materials db that points back to the Transactions file? I ask, b/c I'm not that familiar with portals; I've read about them, think I understand the basic concept, but have never set one up, not like this.

Link to comment
Share on other sites

  • 2 weeks later...

This topic is 8290 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.