Hello FM community,
I'm rather new at FM and was hoping someone could help steer me in the right direction. I'd like to build a simple relational database that I can use to track the prices of grocery store items I routinely purchase. I'm thinking I would create/use a form and manually enter the data from my receipt. At this time, I'm not looking to track prices for items that I didn't purchase, although that may come at a future point.
My initial attempt was to create four tables: "Store", "Product", "Store/Product" (joined table to deal w/ the many to many relationship), and a "Purchase Price" table so I could capture the data/prices from each shopping trip. I'm having trouble figuring out how I should connect/incorporate the Purchase Price table. My current attempt are not giving me the results I'm looking for. From the Products table (form view), I added a portal that would allow me to see the most recent occurrences when I purchase the item including the date, purchase price and store which I made the purchase, but the records are not coming across accurately. The dates are correct, but the store and purchase price just repeat themselves. I played with the various relationships, but it only made it worse.
Does anyone have suggestions for what tables and relationships I could use to get something like this to work? Any feedback is greatly appreciated.
Thanks in advance!!