Appropriate Relationships / Data Model

[JFC70]

Assistance Needed - Creating the right relationships based on the information below.

Being in the print, packaging, promotional and apparel industry, I am trying to build a "One Click" solution for processing client's print JOBS and invoicing them out accordingly.  Each JOB requires the following:

• JOB NUMBER (JOB).  Maintain the same job number from initial JOB SPECIFICATION through INVOICING, and versioning of JOB NUMBER based on changes.  For example:  If I create a new JOB and it's number is 1001 and then I modify it at some point, I would like to capture the initial JOB number 1001 and then have a revised JOB number that reads something like, "1001r2".   This gives me the ability to know the historical changes along the way.  I am not sure this is possible, but any input would be great.
• JOB SPECIFICATIONS. Build out unique specifications tied to the JOB (JOB Name, Description, No. of components that make up JOB, what are all of their specifications - Component Name, Page Count, Flat Size, Finish Size, No of Colors, What Finishing is needed, etc.  Each estimate may have different specification requirements (a brochure, an envelope, t-shirts, and a display stand require some of the same specs, but many different ones too). 
• FORMAL QUOTATIONS to Suppliers. Once specifications are created in filemaker and verified by the client, I will need to confidentially send a formal quotation out to multiple suppliers to BID on the JOB.  These suppliers may bid the whole JOB, or just components of it.  For instance, we may have a direct mail campaign with multiple print components in it.  As a result, one supplier cannot fulfill the whole JOB.  Therefore, we will need to send portions of the Formal Quotation out to suppliers that can complete certain aspects.  On the other hand, we may just have a brochure that needs to be completed and this Formal Quotation goes to just one type of supplier.  
• FORMAL QUOTATION to Client.  Once, all bids are received into our office, then we will create a Formal Quotation to the Client.   We would like to send via email.
• PURCHASE ORDERS (POs). Upon acceptance of the Formal Quotation by Client, then we need to pick and choose the right suppliers to run the JOB.  At this point we will need to produce one or more POs to send out to those chosen suppliers to produce the component.  
• SUPPLIER INVOICES.  I would like to capture all of the suppliers invoices and tie them to the JOB.  Do I need to create an entity to store the supplier invoices to this JOB?  In essence, I would like to scan and track all of the hard copy cost of goods (cogs) and tie them to every JOB.
• INVOICING.   This is pretty self explanatory.   After a JOB is completed by the vendors, we will invoice the JOB to the client.
• CONSOLIDATED DETAILED INVOICES.  Just like above, we may need to Invoice a client once a month for all of the JOBS we did over the past 30 days.

I hope I spelled this out clearly.

With this said, I am asking for input on how to create the most efficient Entities/Table Occurences within the FM Relationship Diagram to have this work successfully.   I have read the Basics and part of the Advanced FM Training Series, but I still cannot get my head around the scenario I listed above.  I think I am making this more difficult than it appears within my industry.   Any insight would be greatly appreciated.

JFC70

 

 

 

[Fitch]

You've spelled it out clearly, but the devil is in the details, and there are going to be massive numbers of details in this project. Maybe you could take one piece of it and ask a more specific question.

[JFC70]

Fitch:

Thanks for the reply.   I know this will have a LOT of details / moving parts associated with it as there are many entities and table occurences that will have to be created, etc.   However, to start the data modeling, I am wondering the best approach for calling out the appropriate Entities for this solution and maintaining a consistent JOB # throughout.  

[comment]

However, to start the data modeling, I am wondering the best approach for calling out the appropriate Entities for this solution ...

To start the data modeling, I would suggest you construct yourself an ERD. Note also that some of your entities seem to be related in a one-to-one relationship and could possibly be united (e.g. Jobs and Quotations To Client).

Keep in mind, though, that an ERD is not the same thing as Filemaker's relationships graph. There are are many possible ways to implement an ERD as an RG, and a lot of room for personal preferences - see: http://fmforums.com/forums/topic/67070-anchor-buoy-graph-model-circular-relationships-many-to-many-relationships-bidirectional-portals/

 

... and maintaining a consistent JOB # throughout.  

Any entity related to the Jobs table, whether directly or indirectly, can reference the related job's unique identifier through the relationship. That's something that's built-in into the application and there's nothing you need to do except make sure you have the right relationships in place.

 

[JFC70]

Thanks for the piece on Anchor Bouy and the definitions between an ERD and Filemaker's graph.  As a novice here, I crossed the terminology in my initial post.   

I am developing an RG.  With that said, based on my scenario in my first submission and your expertise, what would make the most sense to be the "Anchors" within the TO Group?   This is how I see the initial development:

Anchors:

• CLIENT COMPANY
• SUPPLIER (I am not running Client and Supplier as one table since I have so many different fields for each)
• JOB
• SPECIFICATIONS (I have this as its own table since each job will utilize certain specs from here - i.e. Brochure specs vs apparel specs, they are totally different)
• PRODUCT (for Apparel)
• QUOTATIONS (use this for both supplier and client)
• PURCHASE ORDER
• INVOICING

What are your thoughts?

As it pertains to Job Number Updates, PO segmentation to given suppliers, and Consolidated Invoices, I think that is something to look at from a scripting perspective, am I correct?

Thanks

 

[bcooney]

You are undertaking the development of a huge system, and yet you list yourself as a Novice. I worry. You might consider a starter solution:

http://jarviscrm.com

http://fmstartingpoint.com

http://extensitech.com/client-success-stories/filemaker-solutions/solutions/

 

[JFC70]

I understand that this will be a BIG undertaking and will require a lot of time on my part.  However, I do not have the capital at this time.   If I did, I would have jumped on a FM certified solution partner to address this yesterday!

In the meantime, any feedback would be graciously appreciated.  

I sure you understand as well.

Thanks

JFC70

[bruceR]

Well; perhaps we do understand - this may not be the place to get that result. Buy the completed product.

Note that one of the suggestions you have received is a free product - FMStaringPoint.

You're asking people to donate their time to develop a complex product for free for you; and deal with the substantial added workload of not only developing the product but explaining the development process and explaining FileMaker, etc. 

It looks like you are standing by an empty lot with a shovel and telling passersby that you want to build a hotel but you don't know how and you've never built one and by the way you don't have any money.