Unknown issue using Lists, ValueCounts, and Loops

[NewBoard]

I am attempting to use the following script in a Database:
 

Set Variable [$supplierCount; Value: ValueCount ( List ( Suppliers::UUID ) )]
Set Variable [$month; Value: MonthName ( Get ( CurrentDate ) )]
Set Variable [$suppliers; Value: List ( Suppliers::UUID )]

Loop
	Set Variable [$i; Value: $i+1]
	Exit Loop If [$i > $supplierCount]
	New Record/Request
	Set Field [Month::Month; $month]
	Set Field [Month::fk_Supplier; GetValue ($suppliers ; $i )]
End Loop

 

When I run it, however, it creates zero New Records within my "Month" database.

I'm not sure what the issue is. My only hunch is that the List function isn't properly creating a List of the "Suppliers::UUID" field, and is causing the ValueCount taken of it to return 0.

Please let me know of any additional information I can provide, and thank you in advance for any assistance you can offer!

[bcooney]

It's not clear what context you're in or how the tables may be related.

However, the first thing that jumps out to me is your use of List ( ) https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Flist.html

Note, unless you're pointing to a related field, you will not get a list of values. If you are on a found set of suppliers, perhaps you could use

$supplierCount = get (foundcount)

It's also not clear where you're going with this. A Month table?

[NewBoard]

11 minutes ago, bcooney said:

It's not clear what context you're in or how the tables may be related.

However, the first thing that jumps out to me is your use of List ( ) https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Flist.html

Note, unless you're pointing to a related field, you will not get a list of values. If you are on a found set of suppliers, perhaps you could use

$supplierCount = get (foundcount)

It's also not clear where you're going with this. A Month table?

The names of all the Tables/Fields are mostly just arbitrary right now, this is a database that I've just begun to slap together this morning.
It's going to be a Database that keeps track of each of our Supplier's On-Time Delivery for the month.

How would you recommend I get FileMaker to create a List of all Records in a Field?

[bcooney]

"List of all Records in a Field"

not a valid phrase. List of all values in a field, perhaps? That depends on where you are in the relationship graph. So, where are you? On a supplier's layout based on a table occurrence that points to the supplier table?

And, if this is a report of on-time delivery, what data are you reporting? Sounds more like a sub summary report is needed perhaps from a table based on receipts? 

EDIT: I hope I'm not coming across a bit pedantic. It's very helpful to use the proper terminology to be sure that you build your FM knowledge with a firm base. 

[NewBoard]

5 minutes ago, bcooney said:

"List of all Records in a Field"

not a valid phrase. List of all values in a field, perhaps? That depends on where you are in the relationship graph. So, where are you? On a supplier's layout based on a table occurrence that points to the supplier table?

And, if this is a report of on-time delivery, what data are you reporting? Sounds more like a sub summary report is needed. 

Sure all values in a field. The Data is measured in number of Parts. So basically Supplier XYZ delivered 1,000,000 total parts, and 750,000 parts on-time.
I'm on the "Month" table, which is related to the "Supplier" table via Month::fk_Supplier = Suppliers::UUID
I was hoping to have a button that would "start a new month" by creating a new Record in the Month Table for each Supplier in the Suppliers Table. This would then let the user Filter by month and see each Supplier's On-Time Delivery numbers for their particular chosen month.

Edited January 3, 2018 by NewBoard

[bcooney]

Not how I would model this. In what table are you recording receipt of parts?

[NewBoard]

8 minutes ago, bcooney said:

Not how I would model this. In what table are you recording receipt of parts?

I've honestly put this project off for so long because I'm entirely unsure of how to lay this Database out. I need it to be future-proof so that nobody has to create new Tables, Tabs, Layouts, or anything Administrative.
I was hoping to record the parts within the month database. So after running the aforementioned script, you would be able to scroll through and input the data pertaining to each Supplier.

I've attached the excel spreadsheet we currently use to keep track of Supplier On-Time Delivery if that can help you to understand what I need this Database to function like.

2017 OTD Supplier.xlsx

Edited January 3, 2018 by NewBoard

[Agnes Riley]

Get a copy of FM16 advanced  and you will be able to debug your script. Or write it out In English on paper or whiteboard.

I usually set things step by step so I can see where the logic might fail. I see you're not setting the counter variable ($I) to 1. If there is no value in the counter it will not be able to increment it.

[NewBoard]

1 minute ago, Agnes Riley said:

Get a copy of FM16 advanced  and you will be able to debug your script. Or write it out In English on paper or whiteboard.

I usually set things step by step so I can see where the logic might fail. I see you're not setting the counter variable ($I) to 1. If there is no value in the counter it will not be able to increment it.

I've actually used that same Loop logic in other databases, and it works just fine. Variables start at zero when you first create them, so by adding one right away it sets it to one, and then increments it in loops afterwards.

[bcooney]

NewBoard,

OK, so you're in the process of beginning a new FM project and your goal is to report on deliveries, by supplier. I immediately think thru the entire system needed to support such a report, perhaps the following tables:

Supplier (or party)

order

order_line

shipment

shipment_line

parts

Each shipment has a type (incoming for receipts, for example), id_supplier and a date. So, by reporting on the shipment_line table, I can generate your report. No need for a month table (I just group by month of shipment date, supplier). Also, I'd ask the user via a dialog for a date range, and incorporate a find in my script. Still not sure how you determine whether a shipment receipt was "on-time." Where is the date that you expected to receive the shipment? I would expect it's in the related Order record or related order_line record.

To answer your original question:

List (relatedTable::id) provides a delimited list of the related table's ids.

or

Let ([ $query =  “ SELECT
    id
   FROM myTable “

; $result  = ExecuteSQL ( $query ; “” ; “”)

]; $result

) //end let

if you'd like to use SQL

[NewBoard]

Thank you very much for your assistance bcooney, this definitely gives me a good starting point on this project!

[bcooney]

Glad to help. Step back and think data model before diving in always helps. Build a solid foundation.