Wrong Record Being Flagged

[sharingsunshine]

Hello,

I have a problem with a script that I use to flag records that need to be deleted. Because I need the records to be displayed in a preview mode I am using a nav bar script I found thru this forum.

My problem is when I go to flag a record thru the nav bar it flags the previous record and not the one currently displayed. If I run the script without the nav bar it flags the correct one.

I need the nav bar to display the pages correctly. And I need the operators to not have to memorize a bunch of commands for each operation.

I have attached images of both scripts the nav bar script and the one without. Please tell me what I can change to get the nav bar script to flag the current record that is displayed.

Thanks,

Randal

Script_Image.zip

[mz123]

A couple of thoughts:

1. The "Halt Script" step at the beginning stops the script from running at all.

2. Have you considered putting a checkbox for the delete field on the records so that users just have to click the checkbox to mark a messages as "Delete"?

3. Could you send more of your file? I'm not sure I'm fully understanding the issue.

Hopefully I can help!

[sharingsunshine]

Hi Martha,

Thanks for your willingness to help. The checkbox won't work because this is a list of orders and each order has multiple items. So, only in preview mode do you see on the page all of the items that ordered per individual order.

In preview mode you can't have a delete button.

I have included the actual application as an attachment. Just open up orders_converted. All of the data is fictitious. Then just press the Find All button. The Nav bar will pop up use the next record link go to orderid 1330 and then press record delete. You will notice it goes back to orderid 1203 and clicks Yes in the radio button.

I need to know why it is flagging the previous record.

At any time just hit Done and it will break you out.

The other links and buttons don't work the best yet.

Thanks,

Randal

Forum_Zips.zip

[comment]

I am having a hard time following this. It seems like you are confusing pages with records. When you are in Preview mode, you are navigating among pages. A page can contain a number of records (sometimes even no records). When you go to another page, the current record does NOT change, and you will be returned to it when you exit Preview mode.

[sharingsunshine]

My mistake!

I am really wanting to delete the page then.

Then I need to change the nav bar to say next page instead of next record.

Thanks for pointing that out.

Randal

[comment]

There is no such thing as deleting a page. Pages are generated on-the-fly when you go into Preview mode (or when you print). They are meaningless in any other aspect of the database. IOW, you cannot delete a page - because it does not exist.

Perhaps you need to re-examine this part:

Because I need the records to be displayed in a preview mode

[sharingsunshine]

Hello,

I am new to Filemaker so I am sure my terminology is off and I would ask you to forgive me. Let me explain what I am trying to do.

Early on I was instructed that I needed be in preview mode if I wanted to send each orderid with all of its items being ordered on one page.

I receive a flat file consisting of a CSV that I import and then need to display it using subsummary parts. That according to my understanding can't occur unless it is in preview mode.

The operator then needs to review the orders in preview mode so there will only be one page per order. In the other modes if an order has more than one item it will display the same orderid as many times as there are products on the order. For simplicity if the order needs to be deleted I wanted to keep it in preview mode.

Once this list is reviewed it needs to be faxed in preview mode so the order entry personnel will see one page per order with all of the respective items on that page per that orderid.

If there is another way to do that I am open to a different approach.

Hope that makes more sense. Thanks for your help.

Randal

[comment]

Preview mode is good for what is says: previewing what a printed/faxed/PDf'ed report will look like. It is not so good for reviewing DATA, and certainly not for MODIFYING (editing/adding/deleting) data. That's what Browse mode is for.

Normally, you would review your data by browsing the Orders, with the OrderItems being displayed in a portal. I say normally, because if I understand correctly you don't have a table of Orders - only OrderItems.

So there 2 possible solutions for this: either create a record in Orders for each group of imported items (could be done by a script immediately following the import), or build a browsing system that will do essentially what your preview system does now, but in Browse mode - using a self-join to group items in a portal, and buttons to jump to the first record in the next/previous group.

[sharingsunshine]

That seems like a better approach particulary to get rid of the nav bar approach.

However, I don't have anything showing me how that can be done. I went thru the tutorial and it isn't addressed. Do you know any resource that would be helpful to understand how to change this over?

Thanks,

Randal

[comment]

Well, neither one is exactly basic stuff. That's why I didn't elaborate, because you need to pick one of the two before we can proceed. I am afraid I cannot help with that, because it depends on all those things about your project that you know and I don't.

[comment]

Here's a simple implementation of the second method. There is only one table, but the Browser layout gives the illusion of browsing records in a 'virtual' Groups table.

Note that the records must be sorted by group, in order for this to work.

GroupBrowse.fp7.zip

[sharingsunshine]

That sure was generous of you to give me that example. I believe that will be a better foundation to work with.

I have put in the navigation symbols like you had and I noticed when I use them it skips a number in the status area. When I looked at your example it didn't is that something to be concerned about?

I have included my db if you want to look at it.

Thanks,

Randal

GroupBrowse.fp7.zip

Edited February 22, 2007 by Guest

[comment]

It skips records in my example, too - that's exactly the idea, to jump to the first record in the next group. In the actual implementation, the status area should be hidden.

[sharingsunshine]

Thanks for letting me know that was OK.

Is there some special way to produce a one liner report for each group (order id). For Example,

Date of Order, Group, Order Type, First Name, Last Name - this is the other report I have to fax in as a prelude to the order detail report.

I received these instructions back in November here on the forum:

**************

from what you wrote, its seems as if you can create your report with a simple report with grouped data.

Go to New Layout --> Columnar/List Report --> Report with Grouped Data --> Choose the fields you want on the report --> Choose the field you want to group by (in your case Order Number)

**********

I tried that numerous times and I still get multiple occurences of the record if more than one item is ordered.

You have been so helpful I will be glad to pay you or hire you to do these last items. I looked to see if I could PM you and it wasn't where I could find it.

Thanks,

Randal

www.theherbsplace.com

[comment]

For a report showing only a single line per group (order), you would create a layout with a sub-summary part (by group) and no body part.

GroupBrowse.fp7.zip

[sharingsunshine]

Thanks again,

That is a big help.

Lastly, I have looked and even posted a question do you know a way to combine layouts so that I will have both reports in the same fax transmission?

Report 1 - Fax Summary Listing of Orders - Page Break

Report 2 - Fax Detail of Each Order - each order on own page

I looked on the forum and found this post about copy and pasteing into a PDF.

http://fmforums.com/forum/showtopic.php?tid/182440/post/230496/hl/combine+layout/

Wasn't sure if you knew a better way or not.

You have been a great help.

Let me know if I can ever help you.

Randal

[comment]

That is not an easy problem. The Copy Preview method is actually not bad, and certainly the simplest. Another option would be to set a global text field to the contents of the summary report, using a looping script (essentially similar to clicking through the virtual Orders, and appending the current stats to the global field).

[sharingsunshine]

I will check that out this coming week about the copy option. In the meantime, I have been trying to update the field Order Type with the word Drop Ship if it is empty, otherwise just leave it what it is already.

I have the following scripts in place. It works except it won't update the field if there are more than one item per group (order ID). In other words, if someone buys more than one item it won't populate that field.

Here are the scripts

1. Import

2. Import calls the populate script.

Please tell me if you see anthing wrong in my scripts.

Also, please tell me if using Grab is the best way to get a copy of the scripts. I have tried copy and paste and that won't work the way I am used to.

Thanks,

Randal

Forum_Zips.zip

Edited February 24, 2007 by Guest

[comment]

A couple of points:

1. Go to to the desired layout first, then Show All Records.

2. I think you want to set the field in the 'local' table, i.e. the same TO that you are currently in. That would be Items, not GroupItems. The way you have it now, you are repeatedly setting a field in the (first) related record (which is the same one for all the items in the group).

3. You SAY "... otherwise just leave it what it is already" - but you DO "... otherwise MAKE IT EMPTY". Either put your condition in the script itself, instead of the calculation, e.g.:

...

If [ IsEmpty ( Items::OrderType ) ]

Set Field [ Items::OrderType ; "DropShip" ]

End If

...

Or make the calculation return 'what it is already' as the default result:

...

Set Field [ Items::OrderType ; Case ( IsEmpty ( Items::OrderType ) ;"DropShip" ; Items::OrderType ) ]

...

4. I am curious why do you delete all existing records before importing new ones - don't you want to keep a database of your transactions?

---

In ScriptMaker, you have the option to print a script, then preview - and you can copy and paste AS TEXT from the preview screen.

[sharingsunshine]

I have used the option to print a script and it opens in Mac preview but I didn't see the text select. That works great. That will save me lots of time.

I will use the if option. However, I looked in the manual and I couldn't tell what in the line below says to leave what is already there alone. Can you tell me a little more? Also, how did you learn that since the manual doesn't mention it. At least that I understood.

Set Field [items::Order Type ; Case (IsEmpty(Items::Order Type) ; "DropShip" ; Items::Order Type)]

Order Type is two words actually that is why it is changed above.

I made the changes you suggested and they work great.

I don't want to keep the orders on my desktop they are kept in the shopping cart database. I am only downloading the orders to fax them into the manufacturer so they can fulfill the order. That is why I want them deleted so we don't place them twice.

Now I will be doing the combine layout option and then I will be using Parallels to build a PC build and send it to my order processor.

Thanks again for all your help.

Randal

[comment]

I think the help on the If() and Case() functions explains it quite well:

If test is True, FileMaker Pro returns result1. If test is False, result2 is returned.

If ( IsEmpty ( Items::Order Type ) ; "DropShip" ; Items::Order Type )

test = IsEmpty ( Items::Order Type )

result1 = "DropShip"

result2 = Items::Order Type

[sharingsunshine]

I think the help on the If() and Case() functions explains it quite well:

If test is True, FileMaker Pro returns result1. If test is False, result2 is returned.

If ( IsEmpty ( Items::Order Type ) ; "DropShip" ; Items::Order Type )

test = IsEmpty ( Items::Order Type )

result1 = "DropShip"

result2 = Items::Order Type

On result2 is it because this last part Items::Order Type ) doesn't have a semicolon and a value does that make it not change the existing value?

Also, on the copy preview into the page container I have this script and it will copy the summary report but nothing after it.

Build and Send

Go to Layout [ “Fax Report Complete” (Items) ]

Enter Preview Mode

Select All

Copy [ ]

[ Select ]

Enter Browse Mode

Go to Layout [ “Build and Send” (Items) ]

New Record/Request

Paste [ Items::PageContainer ]

[ Select ]

// Delete Record/Request

Go to Layout [ “Browser” (Items) ]

Enter Preview Mode

Select All

Copy [ ]

[ Select ]

New Record/Request

Paste [ Items::PageContainer ]

[ Select ]

It seems to me I need to have something that says to append the preview pages to browser but I didn't see any using that word append. Can you give me some more direction?

Thanks,

Randal

[comment]

I don't understand your first question. If the field is NOT empty, the test is FALSE and result2 is returned. result2 is the field's existing value, so the field is set to its existing value.

I am bit confused by your script, too. This technique works well when the summary report fits on a single page. You take a snaphot of that page and paste into a GLOBAL container field. You cannot append another snapshot to the same container - and you don't need to, because the rest of your report is done in the "regular" way.

In your report layout, you would have either a title header or a leading grand summary part to hold the container field, and after that the parts that form the detailed report. Your script should look something like this:

Go to Layout [ SUMMARY REPORT ]

Enter Preview Mode

Copy [ ]

Enter Browse Mode

Go to Layout [ DETAILED REPORT ]

Paste [ Items::gContainer ]

Enter Preview Mode

No records should be created or deleted in this process. I should add, following an earlier remark of yours, that the results on Windows might be less optimal, due to the snaphot being limited to screen resolution (see here).

[sharingsunshine]

Thanks for the direction on how to do it. I am real close. The summary report works fine. The detail pages that follow show all the order ids but orders with multiple items there is a page for the same number of corresponding items per group (order ID)

Here is the script I put together:

Build and Send

Go to Layout [ “Fax Report Complete” (Items) ]

Enter Preview Mode

Copy [ ]

Enter Browse Mode

Go to Layout [ “Build and Send” (Items) ]

Paste [ Items::PageContainer ]

Enter Preview Mode

Fax Report Complete is the summary

Build and Send assembles the summary and the individual pages in one string to be faxed.

If you can give me and idea on how to get it showing only one page per order ID I would appreciate it.

Thanks,

Randal

[comment]

In Layout mode, double-click the panel that says "Sub-summary by OrderID (Leading)" and turn on "Page break before each occurence".

[sharingsunshine]

I tried what you suggested and I must have my setup incorrect because that only makes the script create a summary page after every order.

I actually have built a separate layout called Build and Send. Was this the correct approach?

I have attached my latest build. So please tell me where I am off.

You can make the error occur by going to order 3101 and seeing how many pages in preview mode you have to advance through, because of the multiple items.

Thanks for all your help,

Randal

Forum_Zips.zip

[comment]

LOL, you seem to be going from one extreme to another. First, you tried to edit your data in Preview mode, using sub-summaries instead of a portal in Browse mode. Now you are using a portal for reporting, where sub-summaries work best.

See if the attached makes sense.

GroupBrowse2.fp7.zip

[sharingsunshine]

LOL, you seem to be going from one extreme to another. First, you tried to edit your data in Preview mode, using sub-summaries instead of a portal in Browse mode. Now you are using a portal for reporting, where sub-summaries work best.

See if the attached makes sense.

Your correct. I was really off base. Thanks for your patience with me.

I am having two problems:

1. Using the browser layout it seems to loose track of what record is next using those buttons if I delete something. If I use the button to advance all the way then back up a few records and then delete one. It doesn't seem to go back to the next record it skips several. For example, go to the end and then find 2920 and then back up one and you will see 2644. Go to 2920 and then delete that record it then advances to 3079 but then if you want to find 2644 you can't. You can only find 2622. Is there a way to correct that?

2. The order total on the detailed report and build and send layouts aren't correct. I looked and saw that Total extended was the total of extended price and I tried to use the SUM function, as an alternative, but then the browser amounts were off. So if you could give me some direction as to how to fix that I would greatly appreciate it.

I have attached my latest changes.

Thanks for all your help.

Randal

Forum_Zips.1.zip

[comment]

There are actually more issues here than the two you raised - and they are somewhat related to each other.

First, my navigation script is over-simplified, and it doesn't work well in all circumstances. That can be fixed.

The second issue is deleting records. This causes the records to become unsorted. The navigation script MUST have the records sorted in order to work properly. This means the deletion must be scripted, and the script must re-sort the record after deleting.

Here a clarification is required on your part: your Delete button is located outside the portal. This suggests you want to delete the entire order (all items in a group). But the action attached to the button deletes only a single record (one item), and the choice of which one is rather arbitrary.

The calculation of Order Total needs to be:

GetSummary ( Total Extended ; OrderID ) + Shipping

This too requires the records to be sorted, but since you won't be deleting records at this stage, it shouldn't be a problem.

Overall, this is getting to be much more complex than I thought. I am beginning to think that it might be more efficient to create an actual record for each order, instead of trying to simulate them.

[sharingsunshine]

I tried what you suggested and I must have my setup incorrect because that only makes the script create a summary page after every order.

I actually have built a separate layout called Build and Send. Was this the correct approach?

I have attached my latest build. So please tell me where I am off.

You can make the error occur by going to order 3101 and seeing how many pages in preview mode you have to advance through, because of the multiple items.

Thanks for all your help,

Randal

I wanted to get back to you and give you an update and ask two questions. I have found a way around the delete record issue. I have the order entry person just use the browser layout to find the records that need to be deleted. Then just go to the items layout to actually delete the records. By placing the cursor on the orderid on a multi item order and using the record delete option all the respective records are deleted and it works great.

My questions:

1. Trying to get the ItemID resorted I came up with this script statement.

// Loop

// If [ ( Items::ItemID ) > 1 ]

// Set Field [ SerialIncrement ( Items::ItemID ; 1 ) ]

// End If

// Go to Record/Request/Page

[ Next; // End Loop

This doesn't change anything so I was wondering about the Set Field statement. Does that need to be changed? I know the looping on a counter needs to be put in place but I was wondering if that is what I would do to reset the itemid to 1 each time .

2. Is there a way to improve the quality of the copy to the page container? After the info is actually faxed the summary sheet is barely legible.

Thanks for all your help,

Randal

[comment]

I'm not getting your script. SetField [] normally needs TWO parameters: the target field, and the calulated value. Also, the SerialIncrement() function isn't appropriate here. If you want to sort records, why not use the (aptly named) Sort Records script step?

In my previous post I have asked for a clarification: will your users always delete an entire order (ALL items of selected order), or could they be deleting only SOME items of the order?

Now, you have also mentioned 'finding the records that need to be deleted'. What does that involve?

Regarding your point 2: I thought it WAS improved in version 8.5 (Macintosh). Is this by any chance on Windows?

[sharingsunshine]

I'm not getting your script. SetField [] normally needs TWO parameters: the target field, and the calulated value. Also, the SerialIncrement() function isn't appropriate here. If you want to sort records, why not use the (aptly named) Sort Records script step?

In my previous post I have asked for a clarification: will your users always delete an entire order (ALL items of selected order), or could they be deleting only SOME items of the order?

Now, you have also mentioned 'finding the records that need to be deleted'. What does that involve?

Regarding your point 2: I thought it WAS improved in version 8.5 (Macintosh). Is this by any chance on Windows?

I didn't just sort the records because it appeared to me that I needed to renumber them in order for the directional arrows to work properly. I will use the sort instead to see if that will make it work better, after all.

The order entry person will always delete the entire order. Even if an order has multiple items or only one item.

Finding the records that need to be deleted is just manually looking at each order that works fine through the browser script.

I am on a Mac but the runtime module is being sent to a Windows user. I remember your mentioning something about Windows having a problem with this. At the time, I didn't understand what the ramifications were about what you said. Also, the link you sent didn't work.

So, is there anything that can be done?

Thanks,

Randal

[comment]

If, in the definition of the relationship, you select 'Delete related records in this table when a record is deleted in the other table' on the "siblings" side, then deleting any single item record will cause a cascading delete of the entire group.

Earlier in this thread I said:

Another option would be to set a global text field to the contents of the summary report, using a looping script (essentially similar to clicking through the virtual Orders, and appending the current stats to the global field).

Which link doesn't work?

[sharingsunshine]

If, in the definition of the relationship, you select 'Delete related records in this table when a record is deleted in the other table' on the "siblings" side, then deleting any single item record will cause a cascading delete of the entire group.

Earlier in this thread I said:

Which link doesn't work?

In post #242159 you gave this link regarding Windows http://fmforums.com/forum/showtopic.php?tid/182123/ when I clicked on the word here it wouldn't come up. Now that I found a way to copy the link to the clipboard I can get it to work. I even checked it again on the word here and it wouldn't work for me.

Nevertheless, it seems it is mentioning what I have ascertained now seeing the results on a Windows system.

In this statement:

************

If, in the definition of the relationship, you select 'Delete related records in this table when a record is deleted in the other table' on the "siblings" side, then deleting any single item record will cause a cascading delete of the entire group.

************

I looked in scriptmaker and couldn't find that option 'Delete related records' am I looking in the correct place?

In your statement:

**************

Another option would be to set a global text field to the contents of the summary report, using a looping script (essentially similar to clicking through the virtual Orders, and appending the current stats to the global field).

**************

This sounds like the best way to go in light of what I have seen about the copy quality on a windows system.

Logically, I can understand what you are saying. however, I am not sure how to loop the virtual orders and then append them. Do you know of any examples that I could consider?

I looked on the forum and filmaker.com and didn't readily notice what I needed to find. This could be because I am a novice, though.

Thanks for sticking with the project,

Randal

[LaRetta]

If, in the definition of the relationship, you select 'Delete related records in this table when a record is deleted in the other table' on the "siblings" side, then deleting any single item record will cause a cascading delete of the entire group.

************

I looked in scriptmaker and couldn't find that option 'Delete related records' am I looking in the correct place?

When Comment said “in the definition of the relationship”, he meant: Open your graph, double-click the = between the relationships. What opens is called your relationship dialog. At the bottom, you will see checkboxes. On the ITEMS side, check the checkbox which says, “Delete related records in this table when a record is deleted in the other table (GROUP ITEMS)”.

But this is a csv of web orders, right? Why aren’t you creating an Order and doing it the right way? Don’t be too sure that you will always delete the entire order! Customers call in all the time wanting to delete only one Item. And what if a Customer calls in and corrects their shipping address? Would you change it in the first Item record? Then what happens if that first Item record is deleted? Same thing when Customer changes their ship method.

Overall, this is getting to be much more complex than I thought. I am beginning to think that it might be more efficient to create an actual record for each order, instead of trying to simulate them.

I totally agree, Michael To solve all your problems, Randal, create your true Orders and parse this puppy correctly on the import. Otherwise you will be constantly struggling to isolate the ‘order’ from its items. If you wish to consider using a true relational structure here, I would be willing to help you through it. It will be MUCH easier to work with.

As an aside … your DateOfOrder is text. I realize that’s how it comes in but you should have a true date field with Auto-Enter which takes that text date and turns it into a true date. Otherwise, how will you ever know what year it happened or produce date-required information from it? Do you have a Customers table? You truly need one of those as well.

LaRetta