Jump to content
tonyMONK

Criteria for selecting records

Recommended Posts

tonyMONK

This probably sounds like a rather simple problem.

 

I am trying to update a table that contains records showing bank transactions from a related table that contains a list of recurring payments i.e. the idea is to periodically update the bank table with the recurring transactions so that they are already there when checking off the bank.

 

There are 2 selection criteria which refer to 2 of the fields on the source table - 1) a date prior to today and 2) to have not been updated previously (this is a yes/no field).

 

As I it I need to select the records that fit the criteria and then copy them into the destination table.

 

The problem is that I don't seem to be able to get the script steps right to perform the find and select (I haven't got a far as the copy stage as yet!).

 

Any help would be greatly appreciated!

Share this post


Link to post
Share on other sites
AHunter3

Enter Find Mode []

Set Field [TableName::DateField, "

Set Field [TableName::Updated, "No"]

Perform Find []

Share this post


Link to post
Share on other sites
tonyMONK

Thanks again for your help - with a bit of fiddling it looks like it might do what I want.

 

HASTA LA VISTA!

Share this post


Link to post
Share on other sites
tonyMONK

Having found the record set that I need I'm now having a few problems in incorporating this into the bank record.

 

I have tried using the following script steps

 

Go to Layout ["Recurring Payments"]

Go to Record/Request/Page [First]

Copy Record/Request

Go to Layout ["Bank"]

New Record/Request

Paste [select; Bank::Field]

 

but of course have found that the entire record contents are pasted to the Bank Field, as opposed to the Bank record. I haven't had much luck with Filemaker Help on this either.

 

Can anyone tell me what the script steps are for copying the "recurring payments" record to the bank record. Do I have to copy field by field?

 

Also I need to copy the entire found set which consist of more than one record, not just the first or last record.

 

Thanks

Share this post


Link to post
Share on other sites
AHunter3

In your first post you said it was a related record. If it's a related record you do not need, and should not be using, any form of Copy function. Just use Set Field:

 

Set Field [bank Transactions::Field X, Recurring Payments::Field X]

Set Field [bank Transactions::Field Y, Recurring Payments::Field Y]

etc

 

 

If the records in question are not yet related (i.e., the tables are related but you have a record in Recurring Payments that doesn't as of yet have a corresponding record in Bank Transactions), then you first need to create a corresponding record:

 

Go to Layout ["Recurring Payments"]

Set Field [AnyTable::GlobalField X, Recurring Payments::Unique ID Field]

Go to Layout ["Bank Transactions"]

New Record/Request

Set Field [bank Transactions::Unique ID Field, AnyTable::GlobalField X]

 

If you've got a double handful of fields with data in Recurrent Trans and you want the data to be copied to Bank Trans and those fields are not lookups, you can then use Set Field:

 

Set Field [bank Transactions::Field X, Recurring Payments::Field X]

Set Field [bank Transactions::Field Y, Recurring Payments::Field Y]

etc

Share this post


Link to post
Share on other sites
tonyMONK

The fields are not related as the records are still in the recurring payments table. In any case I need them to stay there once copied, but the update field will change from "no" to "yes".

 

So what I'm attempting to get into the bank file is the found set (i.e. transactions prior to today's date but not yet updated). The objective is therefore to create a complete corresponding record of the found set in the bank file and to be able to do this periodically . There will always be more than 1 record in the found set.

 

I have the found set and I've tried using your script, alas without success. Do I need a unique ID field or is this just your example of one of the fields to be copied? All I get is an error message saying the date in the bank table is designed to require a value.

 

Is the idea to transfer by way of the global field each field in the Recurring Payments separately or can this be done somehow as a complete record? Is it possible, for example, to copy the complete record to a global field and then somehow get this record into the Bank table.

 

Your last example does appear to get one record across to the bank table, but one only. How can I adapt this to get all records in the found set across?

 

Thanks again for your help and patience!

Share this post


Link to post
Share on other sites
AHunter3

Can we back way off and look at what it is that you're trying to do with two separate tables in the first place?

 

I'm thinking you should not have two separate tables.

 

 

Be that as it may, you can copy a whole slew of records via the same technique as copying one, by enclosing it in a Looping Script

 

Assuming you've manually found the records in Recurrent Trans that you want to copy over into Bank Trans:

 

Go to Record [First]

Loop

Go to Record [Next, Exit After Last]

End Loop

Share this post


Link to post
Share on other sites
tonyMONK

Thanks for the quick reply AHunter3.

 

The 2 tables idea goes back to when I was using DataEase for DOS. The idea is to have periodically recurring transactions in one table and to update these to the bank when necessary, then checking off the record in Recurring Trans as having been updated.

 

When you say that it may not be necessary to have 2 tables perhaps I could just have the bank table which would include the recurring transactions. I suppose then that I could organise this to "see" only the transactions in the bank table up to the current date. I'll give this a try tomorrow (it's getting a little late here) and also your suggested loop script and let you know.

 

Your help and interest is very much appreciated!

Share this post


Link to post
Share on other sites
tonyMONK

Well I tried using one table with the idea of only being able to "see" the Recurring Trans prior to or at the current date.

 

Whilst I can obtain the found set of records on the Recurring Trans layout I have not been successful in seeing only these on the Bank Layout, which has a portal where these are shown.

 

In fact when I manual try a Find in the portal of all transactions

 

The script that I have is

 

Enter Find Mode []

Go to Layout [Recurring Trans]

Set Field [Recurring Trans::date ,"

Set Field [Recurring Trans::Account num , Number ]

Perform Find[]

Enter Find Mode []

Go to Layout [bank]

Set Field [bank::Account num , Number ]

Perform Find[]

Go to Portal rown [select, Last]

 

Is there a way to do this or am I fighting a losing battle with this idea?!

 

Thanks

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use