I have several field dates that account for different data insertions on one record.
However I would like to find a record/ or records say on a specific date in the past. How do I do that?
In find mode, you can create a new request for each element of a complex find. So, enter find mode, put the search date into date field A, create a new find request ( Cmd-N or Ctrl-N ), put the search date into date field B, repeat for each of the date fields. Finally, perform the find. This will create an OR find, that is, find X in date field A OR find X in date field B OR ..., etc. Look at the FileMaker Basics PDF for more information about searching in FMP.
Sections A through E should be records in a related table. If you want to find a record with a date in one of those sections, you could then specify the date only once in the date field shown their, either in this layout or on a layout based on this related table.
Thanks for all your help and responses; but I cannot figure it out as yet.
I have over 60 dates on each record and over 200 records. =12000 dates to choose from. How do I find A date in this huge selection?
I could not figure out what to do from the responses I got.
I am suggesting that the design of your table is complicating the issue. Many of those date fields should not be in the same record to begin with.
I see only six fields clearly labeled as dates in your screen shot and each should be in a different record. One would be in a parent record, the others would be in separate records of a related table.
Unfortunately you have stumbled into a beginner's pitfall. If you have 60 date fields on the same record then you have a problem that you have to rectify. Whenever you find yourself duplicating fields you should consider putting the duplicated fields into a related table. When you put the fields into a related table you can use the one set of fields over and again, accessing them via a portal. The additional benefit is that you only have to search in one place, not sixty. As PhilModJunk has suggested, the solution for you is to move the appropriate fields to a related table and then shift the data.
What I see has problems that go far beyond finding records by specifying a date. It appears that you have combined client info, invoices and line items in a single record. This will cause a great many problems for you.
I would set up at least these tables:
I might need another table, coupons to link to LineItems depending on what needs to be done with this solution.
The Match Fields:
Clients::__pkClientID = Invoices::_fkClientID
Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
__pk is my personal naming convention for primary key fields. They should be defined to auto-enter a UUID or serial number.
The typical way to work with this information is to base the layout on Invoices with portals to line items. Sections A thru E in your screen shot would each be a different invoice record and the "c" fields listed in those sections would be replaced by a portal to line items where each record/row of that portal would record the sale of one coupon.
Coincidentally, this greatly simplifies the process of searching out records by date as the total number of fields would be greatly reduced. At the same time, the different Date fields in the different parts of your original layout that now fall in different tables with this new design each record a date for a different reason. You ordinarily would not need to specify a specific date in all of them for the same find for that reason.
I have placed a RED circle on the date fields; 66 in all on each record. These dates are inserted each time a person uses our place; there are 66 times one can use our place (ENTER) in one year. However when on a spread sheet, I would arrange up or down by date and physically see or count the number of those who came over in any one given day (date).
From your responses it seem that I have to make a related table. Is that correct?
vic2046 wrote: From your responses it seem that I have to make a related table. Is that correct?
Yes, that's right.
You might want to explain what the different fields are doing in that layout. It will help us provide the most appropriate advice.
I have several date fields in each record; some could overlap. I.e. they
buy and use a coupon on the same day,
1 one is when a person joins us, (1 date)
2 another when he/she visits our place and pays by cash or coupon., (1 to
3 another on the date that he/she bought a set of coupons; 10 to 30 max
50. ... (1 to 5 dates)
Hope that this helps. Victor
On Tue, Aug 22, 2017 at 10:35 PM, Malcolm Fitzgerald <email@example.com>
And what do you do when they want to buy coupons on a sixth occasion?
That question illustrates the fundamental weakness of your design. It has built in limits that create problems when they are reached. There are other problems with this design when it comes to reporting.
I've previously recommended (In an earlier thread) that you needed tables of related records.
In your list you have a date for joining, a date for purchases and a date for visits. That means you need three date fields. Unless people join more than once, you can put the date of joining into the client table. You can expect people to make more than one purchase, so there should be a table for purchases. You can expect people to visit more than once, so there should be a table for visits.
Retrieving data ...