Loop (or For Each...) Help Please

[R81]

Hi All,

I am really stuck on a particular part of my project and was wondering if someone could help.

I have sample of the script below (full script much bigger but this outlines the requirement)

Set Error Capture [On]
Set Variable [$Data1; Value: Table1:Field1]
Set Variable [$Data2; Value: Table1:Field2]
Set Variable [$Data3; Value: Table1:Field3]
Set Variable [$Data4; Value: Table1:Field4]
Go to Layout ["Delivery Notes"]
New Record/Request
Set Field [Delivery Notes::Customer ID; $Data1]
Set Field [Delivery Notes::Ship To; $Data2]
Go to Layout ["Delivery Note Items"]
New Record/Request
Set Field [Delivery Note Items::Quantity; $Data3]
Set Field [Delivery Note Items::Detail; $Data4]

So the issue is this...

• The first 2 variables ($Data1 and $Data2) only need to be entered into the Delivery Notes table once as they customer details that will go on a form.
• There could be multiple records found in the set for $Data3 and $Data4 so i need them to copy each field for each record then paste them into Delivery Note Items creating corresponding records

The outcome needs to have a form (Delivery Notes) which will have $Data1 and $Data2 on it with a portal to Delivery Note Items showing all records for $Data3 and $Data4.

Running the above on its own with 1 record existing for $Data3 and $Data4 works fine, but i think i need a loop or For Each statement to capture what i need but i cant figure it out.

Any help would be much appreciated.

Thanks in advance

Edited February 26, 2016 by R81

[comment]

I got lost trying to follow your description. I have a vague impression you need to do (not necessarily in this order):

1. Import all? found? records from Table1 into a Child table (or perhaps leave them where they are, and let Table1 be the Child table?) ;

2. Create a record in a Parent table for each unique value of ?? in Table1;

3. Pass the ParentID value of each such record to the corresponding Child records, to make them related.

 

 

Edited February 26, 2016 by comment

[sreese]

R81,

 

You could use a executeSQL statement to gather your results.

 

For example:

# Instead of doing this
// Set Variable [$Data3; Value: Table1:Field3]
// Set Variable [$Data4; Value: Table1:Field4]

# You could do this:

Set Variable [$Data3-4; Value: executeSQL ( "select field3, field4 from table1 where id = ?" ; "," ; "¶" ; table1:idfield ) ]

 

With this then you could do a loop:

# Get the total number of records
set variable [ $total ; GetValueCount ( $data3-4 ) ]
set variable [ $counter ; 0 ]

Loop
set variable [ $counter ; $counter + 1 ]

Set Field [Delivery Note Items::Quantity; GetValue ( $data3-4 ; $counter )]
Set Field [Delivery Note Items::Detail; GetValue ( $data3-4 ; $counter )]

Exit Loop If [ Counter = $total ]
End Loop

You'll have to separate the two fields, but that should work.

[R81]

Hi,

Apologies if i wasnt clear in what i am trying to achieve,  I am quite new to Filemaker.

I have 4 tables (Jobs, Job Items, Delivery Note & Delivery Note Items)

Jobs - Contains customer information (fields CustName & PostCode)
Job Items - Contains multiple records (fields Quantity & Detail) all linked via JOB ID back to the Jobs table.

So for the above there is a form (Jobs) which has a protal to Job Items showing all records that match the JOB ID.

Delivery Note - I need to create a new DELIVERY ID and copy the customer information (fields CustName & PostCode) to here.  Then:
Delivery Note Items - Copy the DELIVERY ID from the above into a DELIVERY ID MATCH field and copy the all Job Items (Quantity & Detail), multiple records, to here using the same Delivery ID (to ensure they match to the same delivery).

So for the delivery note there is a form (Delivery Note) which has a portal to Delivery Note Items showing all records that match the DELIVERY ID.

So there are 2 issues.  One:  I only need to copy the Delivery Note data once (which is fine using the Set Variable / Set Field commands) and Two, the part i am struggling with:  I need to loop (?) copy all Job Items to Delivery Note Items and include the DELIVERY ID in each of these records.

Hope that explains it a little better?

Thanks,

Edited February 26, 2016 by R81

[sreese]

You can use the SQL statement to gather all of the additional information quickly into the variable. Then from that you can build the loop like I did above and create a new record for each iteration.

You can do it with portal rows too if you have the relationship setup to allow it. I always just go to the table and create all of the new records.

[R81]

OK thanks sreese, I will put some time into it tomorrow using the above.

[comment]

Why do you need to do all this replication of data? I would have thought you would create a new delivery note only when some items are being delivered. If these items were the items in the current found set, it would be trivial to create a new record in Delivery Notes, load its ID into a variable, come back to Job Items and populate each item with the value in the variable. I don't see why you need the Delivery Note Items table; what's there that isn't in the Job Items table?

I would also link the Delivery Notes table to Jobs, and have it lookup the CustName and PostCode from there.

[R81]

The Delivery Notes/Delivery Note Items contain only a small number of fields compared to what is in the Job Items.  For example, the job items has nearly 50 fields, most of which are for internal use only, however for the delivery note it just needs 3 item fields that go out to the customer.

Also, if you only do a lookup and further down the line the details change (customer name, address, phone) then it will only lookup the changed details, not the old ones.  The company that i am doing this for has insisted that cannot happen due to auditing requirements. So copying the data into seperate tables for invoicing, delivery, quotes etc. seems logical ?

[comment]

50 minutes ago, R81 said:

the job items has nearly 50 fields, most of which are for internal use only, however for the delivery note it just needs 3 item fields

That's no good reason to create a new record in another table with just those three fields. If you only need three fields out of 50s, then use those three fields and ignore the 47 remaining ones.

 

52 minutes ago, R81 said:

Also, if you only do a lookup and further down the line the details change (customer name, address, phone) then it will only lookup the changed details, not the old ones. 

I am afraid you misunderstand how a lookup works. Or perhaps you don't understand what a lookup is?
http://www.filemaker.com/help/14/fmp/en/html/relational.12.20.html#1033096

[R81]

9 minutes ago, comment said:

I am afraid you misunderstand how a lookup works. Or perhaps you don't understand what a lookup is?
http://www.filemaker.com/help/14/fmp/en/html/relational.12.20.html#1033096

Indeed you are right.  As I said I am new to FM and when you were talking about lookups I assumed it was something else.

[R81]

I've been looking at the lookup variable and can see how it would work if all quantaties were being delivered.  However, I cant see how this would work if not all quantities are being delivered.  For example, 2 records found in the set in Job Items

Qty: 4      Detail:  Something
Qty 10     Detail:  Somthing Else

If only 2 of the first record and 5 of the second record are being delivered how wold this work in a lookup?  What i have now is a script to deduct the number being delivered from the total qty and copy the details to the DNI table.  So another delivery note would take care of the remaining items when generated.

[comment]

12 hours ago, R81 said:

cant see how this would work if not all quantities are being delivered.

In such case you would need the Delivery Note Items table.

I am not sure what you preferred workflow is. I guess you would want to create a new Delivery Note first and place its DeliveryNoteID into a global field or a variable. Then, as you select a Job Item to add to the  Delivery Note, you would do something like:

Set Variable [$jobItemID; Value: JobItems:JobItemID]
Set Variable [$quantity; Value: JobItems:Quantity - Sum ( DeliveryNoteItems::Quantity )]
#
Go to Layout ["DeliveryNoteItems"]
New Record/Request
Set Field [DeliveryNoteItems::DeliveryNoteID; Jobs::gDeliveryNoteID]
Set Field [DeliveryNoteItems::JobItemID; $jobItemID]
Set Field [DeliveryNoteItems::Quantity; $quantity]
#
Go to Layout [original layout]

 

[R81]

Using the method that you put above is exactly what I'm after but ensuring that it captures both Job Item records, in the above example, and creates 2 new Deliver Note Items with the Quantity/Detail data in it.

This is where I assumed a loop may work but I can't figure out how.  I assume it would require a loop at the Set Variable stage for capturing all Job Items data (qty/Detail) and then a loop At the Delivery Note Item record creation stage with the Set Field variable for qty/Detail?

Thanks for helping BTW.

[comment]

Do i understand correctly that you want to create a Delivery Note Item record for every Job Item of the current Job (or perhaps every Job Item that has a remaining, undelivered, quantity) - instead of selecting them individually, as I have assumed?

If yes, then try it this way (this assumes your script starts at a layout of Jobs):

Set Variable [$jobID; Jobs:JobID]
#
Go to Related Record [Show only related records; From table: “JobItems”] 
Go to Record [First]
Loop 
  Set Variable [$quantity; JobItems:Quantity - Sum ( DeliveryNoteItems::Quantity )]
  If [$quantity > 0]
    Set Variable [$jobItemIDs; $jobItemIDs & JobItems:JobItemID & ¶ )]
    Set Variable [$quantities; $quantities & $quantity & ¶ )]
  End If
  Go to Record [Next; Exit after last]
End Loop
#
Go to Layout ["DeliveryNotes"]
New Record
Set Field [DeliveryNotes::JobID; $jobID]
Commit Records
Set Variable [$deliveryNoteID; DeliveryNotes:DeliveryNoteID]
#
Go to Layout ["DeliveryNoteItems"]
Loop
  Set Variable [$i; $i + 1]
  Exit Loop If [$i > ValueCount ( $jobItemIDs )]
  New Record
  Set Field [DeliveryNoteItems::DeliveryNoteID; $deliveryNoteID]
  Set Field [DeliveryNoteItems::JobItemID; Get Value ( $jobItemIDs ; $i )]
  Set Field [DeliveryNoteItems::Quantity; Get Value ( $quantities ; $i )]
End Loop
#
Go to Layout [original layout]

Caveat: this is typed out of my head, not copied from an actual script.

Edited February 28, 2016 by comment

[R81]

Exactly.  Let me put that into my project and I will get back to you, thanks

[R81]

Hi - It worked!

I had to do a tiny bit of tweaking to take into account other areas of the script which i didnt discuss with you but the 2 loops are working exactly as I needed.

Thanks a lot