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

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!

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

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.

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

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

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

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.

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

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.

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

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, 200718 yr by Guest

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.

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

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

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

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).

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, 200718 yr by Guest

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.

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

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

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

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).

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

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

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

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

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

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.

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

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'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

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?

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

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

One other thing I noticed ... you have the Customers credit card information right within the Order. This is very bad idea. I assume you include a copy of the order with the shipment. What happens if the box is delivered to a neighbor because the person isn't home? It happens all the time with UPS. What happens if the neighbor isn't a nice person and opens the box? It happens all the time. You have then compromised your Customer's credit card information. What happens if your Customer's son (who is on drugs) gets the package and has a hay-day with it? What happens if you print your orders and the printer jams and the batch has to be reprinted - and some of those orders are tossed in the trash can? Then someone breaks into your office and sees the orders in the trash can (or you have an untrustworthy employee)? You have again compromised your customer's information.

If you plan to store the credit card information, it should never appear in an email, faxed, or on a printed document. It doesn't appear that the CC info comes in your import - it shouldn't. Neither Authorize.net, Order Dog or EDI sends this information - it can be intercepted too easily. Your db should be password protected and you should have good firewalls and the cc info truly SHOULD be encrypted as well.

L

Edited March 9, 200718 yr by Guest

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.

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

Hi LaRetta,

Thanks for your suggestions. Before we go to far, I need to clarify that this is an after the fact order entry system. The customer has used our shopping cart and placed their order. We manually process the credit card and fax in the orders to the manufacturer. They do the fulfillment and charging of the card through their merchant account.

That is why the card number is showing up on the browser layout. Yes, it does come from the import.

The orders are faxed to the manufacturer and we never print them so there is nothing left after it is faxed and confirmation is received. No security risk on the card this way.

I appreciate the explanation on the graph and checking the items section I had it checked already and the other side too. I will make that change to get it correct.

Normally, I don't have customers calling because they think the order is already completed but the very few we do have can be processed manually.

I have started using the app and it is working fine except for the quality of the copy on a Windows system. So rather than spending the time redesigning, I think it will be best to redesign only if I run into an insurmountable problem.

I am making the above statement based on the fact I have been on this app for quite a while and switching to a relational approach sounds like it could be lengthy. I realize you know more about FM's ability than I do. So, could you give me an overview of what I would need to change keeping in mind I get a CSV file from the cart as my import file.

You are very kind to suggest these improvements and be concerned that I may have some security issues.

Thanks,

Randal

Edited March 9, 200718 yr by Guest

I didn't realize you weren't the seller. The process Michael suggested should work fine then; you have no additional need for the information, right? If it is just a different approach to produce your Invoices then no - I probably wouldn't change it. I simply don't know enough about your business or your needs to make that determination for you.

I would mention two additional things ...

1) the possibility of importing duplicates - wait for the day the order number counter accidently resets itself or the web customer decides to start their order numbers over. I switched my process and now import all into an intermediate table where even deletions are kept. Then I check against a match relationship to orders before pulling the new in. When employees download/import csv's from web, they can overlap.

2) You mentioned deleting orders. Are they orders that were cancelled online before you received them or are they call-ins? I ask because SOMEONE should keep an audit trail of these orders. Things will come up where you might like to have proof or at least be able to confirm when/by who the order was cancelled. Otherwise you might get a call from one end or the other wondering about an order.

Glad to hear you're on the move with it now.

LaRetta

Hi Laretta,

I am actually having trouble with how to proceed with removing the copy and paste of the summary page and changing it to what Michael outlined here:

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).

It seems to me that I would use a loop and read each record but how do you post into a global text field and append the information at the same time.

Esentially, I am only wanting to produce a summary page that looks like this:

Order ID Date of Order Order Type First Name Last Name Acct. Number Order No.

The last two fields the manufacturer fills them in so they are just formatted with a blank line. All the column heading are all on the same line too.

I searched on the forum for +"global text" +append but nothing came up that really seems to be close. Do you have any other suggestions?

Thanks,

Randal

I put this together as to what I could find out about appending and global text fields.

Global Build

Enter Browse Mode

Go to Layout [ “Items” (Items) ]

Set Field [ Items::gText[" "] ]

Loop

Set Field [ Items::gText[(Items::Group & Items::Date of Order & Items::First Name & Items::Last Name & "Account Number" &

"Order Number")] ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

Insert Text [ Items::gText ]

Enter Preview Mode

Go to Layout [ “Build Global” (Items) ]

When I run the script it doesn't display the global text field in the Build Global layout.

Please advise,

Randal

That's not bad for a first attempt, but it has a few problems:

1. Before looping, find the records you need (I guess that would be Show All Records in your case), sort them and go to the first record.

2. You only want to loop between ORDERS. If your loop goes to the next record, it visits all ITEMS. Instead of a summary by order, you will get a full report of all items. Use the script that jumps to the first item in the next order instead of Go to Record/Request/Page [ Next; Exit after last ]. The tricky part here is knowing when to exit the loop - I'll try an address this later.

3. The calculation that sets the gText field has to be along the lines of:

gText & OrderField & tab & GetSummary ( ItemField ; OrderID ) & ¶

4. The Insert Text step after the loop is peculiar, to say the least. After you have compiled the summary into gText, you should go to the report layout. The gText field should be placed on the report layout instead of the container field.

That's not bad for a first attempt, but it has a few problems:

1. Before looping, find the records you need (I guess that would be Show All Records in your case), sort them and go to the first record.

2. You only want to loop between ORDERS. If your loop goes to the next record, it visits all ITEMS. Instead of a summary by order, you will get a full report of all items. Use the script that jumps to the first item in the next order instead of Go to Record/Request/Page [ Next; Exit after last ]. The tricky part here is knowing when to exit the loop - I'll try an address this later.

3. The calculation that sets the gText field has to be along the lines of:

gText & OrderField & tab & GetSummary ( ItemField ; OrderID ) & ¶

4. The Insert Text step after the loop is peculiar, to say the least. After you have compiled the summary into gText, you should go to the report layout. The gText field should be placed on the report layout instead of the container field.

Thanks for the suggestions:

Here is the script I have now:

Global Build

Enter Browse Mode

Sort Records [ Specified Sort Order: Items::Group; ascending

Items::ItemID; ascending ]

[ Restore; No dialog ]

Show All Records

Set Field [ Items::gText[" "] ]

Perform Script [ “Browser” ]

Perform Script [ “Group Navigation”; Parameter: 1 ] // this is liking pressing the button to return to the beginning.

Loop

Set Field [ Items::gText[items::Group & "tab" & GetSummary (Items::Total Extended ; Items::Group) & "¶"] ]

Perform Script [ “Group Navigation”; Parameter: "Get RecordNumber + Count" ] //this advances that layout one order at a time

End Loop

Enter Preview Mode

Go to Layout [ “Build Global” (Items) ]

this statement:

Set Field [ Items::gText[items::Group & "tab" & GetSummary (Items::Total Extended ; Items::Group) & "¶"] ]

I wasn't sure which fields to pull from the GetSummary statement said it has to be a summary field so I just took one to try and see what happened. I am really not sure what I am doing with this statement either. I thought I had to append all the field names like I had in the earlier script.

Currently if I run this script after 4 minutes it is still showing that it is active. I have had to stop it each time. Doesn't seem like it should go that long.

Thanks for your help on this,

Randal

Randal, take a look at the attached. I even put it in your file. Yellow button on Browser page. Just paste that field into your summary. This turns the summaries of your orders into columns in one global text field. I believe this is similar to Comment's suggestion.

Fix that date! It drives me nuts to see it without a year!

UPDATE: Oh, your loop won't exit because you have no condition to tell it to exit. You can use many techniques depending upon what you are doing ... usually if you are looping through records, you would use Go To Record/Request/Page [ Exit after Last ; Next ], as Comment mentioned above. But there is also a specific script-step called Exit Loop If [] which you can use other tests to determine when to exit (or you can use an If[] test itself. In my script, I exited the loop when there were no records left (the test of not Get ( FoundCount). I didn't loop through the records at all.

LaRetta :wink2:

GroupBrowse3.zip

Hi LaRetta,

Thanks so much for that code. That looks great. I will look into what can be done about the date. We actually don't keep any of the faxes once the manufacturer has assigned an account number. So no need to know the year.

However, I want to have good code so I will see what I can do.

I will be studying this deeply to see how you did it on Monday.

Thanks for the help.

Randal

Hi LaRetta,

I am trying to format the summary report from the script you gave me. I can get it working except when the name fields are different sizes. I have looked on the forums under global format align and other terms. However, they talk about using a layout with the global fields but because this script uses only one I am at a loss how to adress the formatting question.

I am trying to find the answer but I guess I really don't know the search terms I should consider.

Can you point me in the right direction?

Thanks,

Randal

Hi Randal,

If I understand you correctly, you are having trouble lining up the columns? When within the one field, you need to use tabs. Tabs can then be defined as either left, right, center or center on decimal. You need to plan for the widest of each value (including the field name). If any value is greater than the tab buffer allowed, it will skip that tab and move the data to the next one thus wonking your alignment.

To get an idea of the tabbing, select the global text field in my sample, select Format > Line Spacing and Custom. Then click the Tab button and it will display the current settings. The tabbing function in FM is not very intuitive - it'll take a bit to get used to how it works. But as you make changes and see the effects, it'll make sense pretty quickly.

All in all, you could have had a relational structure in place and functioning by now. I thought this might be an example where you wouldn't need it but, as I said above, if you have ANY need to manipulate the data in FM, relational is better. Sometimes taking shortcuts takes longer. I should have pushed harder to turn you relational ...

LaRetta

LaRetta,

That was a big help. I had no idea the tab control was available like that. I have attached my latest build. You can see I got the fields and headings to line up nicely.

As you know, I am trying to replace the container field because of the poor copy quality. What you have done for me makes the summary page much more legible since it isn't a copy. Once again thanks for all your help on that.

Now I am trying to add the gText field to the top of my detailed report that outlines each order on a separate page. I have used the same subsummary part we used for the container field that held the copy from before. However, when I run the Global Build and Send script I only get a list of the products ordered. With no regard to the subsummaries I have set up.

Am I approaching this in an incorrect manner because it isn't a container field? If you could take a look and point me in the right direction that would be great.

Thanks,

Randal

Forum_Zips.zip

I have no idea which script you are firing - you have duplicates. And you have duplicate layouts and I have no idea which layout you are using. If you are using the Global Build and Send, you are going to your Browse layout (with the portal) which is based upon GroupItems. You need to be on a layout based upon Items because you want ALL items to appear in the body and you don't want a portal. Re-read - and tear apart - the demo Comment posted here (above)

02/26/07 02:56 PM - Post#242379

In response to sharingsunshine ... where he says:

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...

He has provided a perfect demo for you to use. Your Global Build and Send 2 goes to your Detailed Report which has no leading part based upon Group! You should be using the Build and Send layout (but not that script) for the final display. Why did you disable Show All Records at the beginning? Without it, you might miss some records in the looping script. You later have Show All Records (because my script ends up omitting all records) but that unsorts them. If you use the Global Build and Send script and make the following changes, you should be closer:

1) Re-active Show All Records at the beginning. 2) Go to Layout Build and Send (and move the sub-summary part up until it's right under the field labels). After the Show All Records at the bottom of the script, sort them again by Group*. 3) Only your last two 'Orders' have multiple items on them and it appears to display as you have wished.

Tabs apply to fields, so if you have a different layout which tabs correctly for you (gText) then copy/paste that copy of the field into the leading grand summary, so you don't have to re-specify your tab settings. You are also using some spaces in the field labels (gText). I recommend against it. If you change those field names, it'll wonk out on you.

* You could also change my script to jump that number of records forward instead of omitting them but I would rather not confuse issues further - let's get you through this.

LaRetta :wink2:

LaRetta,

Your a great help! Thanks.

It works like it should. I am sorry about the duplicates I should have been more explicit that I was using the Global Build and Send.

I just had the wrong layout being used.

Let me know if I can ever help you.

Randal

LaRetta,

I am in my last stages of deploying the application. I ran across this problem with the PC version.

It works fine for the Mac but on the PC it is not keeping the columns aligned if a person't name is shorter than the longest name in the list.

I have a picture attached.

I reread what you said about making this relational. I am not opposed to that but I am just confused as to how I would do that with one flat file that gets downloaded daily. If it doesn't take too much of your time can you give me an idea so that I might consider pursuing that objective for the next revision.

Thanks,

Randal

Forum_Zips.zip