I wanted to issue a unique number (Like Type+Initials+No+date) and use that in other tables to keep track of Membership and also Lessons.
That's an unnecessary complication best avoided. Use an auto-entered serial number as the value to link records by member ID.
There are two workable approaches that come to mind:
1) Link each record in receipt to detail tables for Memberships and lessons where you record additional data (date when lesson is used, date when membership expires...) A receipt ID can be yet another serial number field and can link a receipt to a record in one of these tables. The relationship from detail to receipts to Members can be used to identify the person that purchased that lesson or membership.
2) Add extra fields to Receipts that record this additional info. Leave the fields empty when they are not needed for a given item in Receipts.
Both methods work, there are pros and cons with either method. In general, if you just need a few extra fields for your memberships and lessons, option 2 may be the simpler approach. But as you find more and more detail info is needed to document them, 1 becomes the better option.
What is the best option if I'd like to use a portal to view all the details of various types of purchases in one member record? And can 1 primary key (Member #) be used as a match field in more than 1 table (like I have in my pic) or do I need a different key to sort of chain link them together? The member # is an auto enter serial.
I'm thinking I may just add more fields to my Receipt table and have the different types of purchases under tabs in the Receipt layout, so long as I can still do another layout that shows just Memberships, and just private lessons, etc. Am I on the right track?
Many many thanks!!!!
As long as you are uniquely identifying a member, a single primary key field in Members can be linked to foreign key match fields in as many other related tables as you need.
A Portal to show the purchase details would seem to be a portal to Receipts, placed on your members layout. This would be exactly the same with either method 1 or method 2.
You may, however want to set up a table of invoices linked to a related table of line items so that a single transaction that purchases multiple items can be set up as a single record linked to members:
My Line items are 1 of 4 items - Memberships, Lessons, Goods(Gear), and Misc Fees. So if I link these detail tables to the invoice table, I can still use a portal from members to display all my line items... and so detail tables seem the best option?
And if so, I would use a primary key in the receipts to link to line items? Do I use a PK to link Members to Receipts, and then use another PK to link Receipts to the different Detail tables?
And then use a portal to view all purchases by member by way of Receipt#? And then I can still go to each detail table when I need to send out renewal emails, or restock Gear or check up on unused lessons?
I very much appreciate your help on this!!
Your detail tables would link to the line items table rather than the invoices.
With invoices and line items tables, you can use a portal to or a list/table view layout based on Invoices to get a list of all purchase transactions. You'd use a portal to or a layout based on LineItems to get an itemized list of every item purchased--all the items purchased on a single invoice, all the items ever purchased by that member or all that member's items over a specific date range are possible.
Sooo, would it be linked like:I insert a table to link the receipts and the different detail tables based on Member#? And this would be by way of a portal on the Receipt table to pull in line items? I can see the "invoice" start solution in FM11 but I think that's bit more than I need or maybe that level of detail is what I do need, but at this point, I'm not sure.And given that I may be entering "Over my head" territory, would you recommend just sticking to adding fields to Receipts?
I insert a table to link the receipts and the different detail tables based on Member#?
I don't see a need for detail tables for fees, goods and gear. You would also use one more table linked to line items that serves as a combined list of all items that a member might choose to purchase where you have one record for each type of lesson, each fee, each item of gear, memberships etc. This is your "price list" where you can look up the current price charged for each item.
You can see examples of this in the Invoices starter solutions that come with FileMaker 11 and 12. The starter solutions, however, will not show the detail tables I have suggested that you might use.
And this would be by way of a portal on the Receipt table to pull in line items?
I think there's some confusion here over "detail tables" and the "products" table you'll find in the invoice starter solution. These are not the same table an serve different purposes. The detail tables are where you'd record additional details such as when a membership expires or when a lesson was actually used. The "products" table in invoices lists all items that can be purchased (including memberships, lessons and fees) for purposes of looking up the current price for each and also for tracking inventory levels on physical items such as your "gear".
And given that I may be entering "Over my head" territory, would you recommend just sticking to adding fields to Receipts?
I can't recommend one way or the other. It depends on how you need this to work. I do think that you need a lineitems table for listing each item purchased but whether or not you should just add fields to the line items records instead of linking in detail tables for that data is not something I can recommend on given the current limited view of what you need to do to support your business.
Thank you for all your help. I realize it's not easy to explain to newbies, and I appreciate all the time you took to explain. I will look further at the starter solutions and see if I can make this work.
Many many thanks!
I looked further and found I had set the id field up wrong - the tables are all relating, so thank you!
I do have another question - I would like to have the value of one field populate based on the text of another - but jumping one ahead in the value list.
For instance - a field named Size with a drop down menu based on a value list with the values " 01 Small, 02 Medium," etc and a field named Next Size Up - would automatically populate with the next size up. If field 1 had "01 Small", then field 2 would populate "02 Medium". Can you populate a field on a text based value list?
Many thanks for any guidance!
A calculation could be used to extract the next value in your value list or if your values are defined with the "use values from a field" option, a relationship could be set to match to the next larger value.
This calculation--can be used as an auto-enter calculation or in a field of type calculation--can return the next value in the value list:
Let ( [ v = YourValueListFIeldHere ;
VL = ValueListItems ( Get ( FileName ) ; "YourValueListNameHere" ) ;
p = Position ( VL ; v ; 1 ; 1 ) ;
GetValue ( VL ; Patterncount ( Left ( VL ; p ) ; ¶ ) + 2 )
Wow, thank you.
You are awesome!
I am still working on this database, and a new question came up - and I hope I can pose the correct question.
I'd like to track the "Contract End Date" though only on current contracts. A Member can have several contracts but I only care about the most recent. I have two tables - one for Members and one for Membership contracts. I have a date "Contract End Date" in the Memberships table, and I have a Current Contract End Date in my Members table. I would like to pull that Date field in from the Memberships table but only if the contract is the current one.
I was thinking maybe if I had a checkbox for current, I could do some sort of if/then?
Many thanks for any guidance.
You can set up a relationship to contracts that matches records both by Member ID AND by a pair of additional fields that work to omit any contracts for that member that have expired:
Members::__pkMemberID = Contracts::_fkMemberID AND
Members::cToday < Contracts::Contract End Date
Define cToday as an unstored calculation with: Get ( currentDate ) and select Date as the result type. You can add a new table occurrence of Contracts fo this relationship if you need to keep your current relationship between Members and Contracts unchanged.
Another option is to use the original relationship and use
Get ( CurrentDate ) < Contracts::Contract End Date
In a portal filter.
As always, you are awesome and I deeply appreciate your help!