Wrong Record Being Flagged

[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, 2007 by Guest

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

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

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, 2007 by Guest

[LaRetta]

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

[sharingsunshine]

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

[sharingsunshine]

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

[comment]

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.

[sharingsunshine]

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

[LaRetta]

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

[sharingsunshine]

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

[sharingsunshine]

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

[LaRetta]

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

[sharingsunshine]

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

[LaRetta]

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:

[sharingsunshine]

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

[sharingsunshine]

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

[LaRetta]

Windows and Mac handle fonts a bit differently. When viewing a field on a PC vs. viewing a field on Mac, the PC increases the size of the font within the field. Many Developers aren't aware that, when a Windows User views a text field, part of the text is cut off. It looks fine on their Mac but Windows handles it differently. Many well-known public demos produce a splash screen (with a few paragraphs of text about the solution). But, on Windows, the bottom 3 lines are cut off and unreadable! Some Developers even lock their solutions in that state so I can never enter the field to pop it so it can be read. It's a personal irritation of mine.

What font are you using? I don't have it appear in my font list and I wonder if it is x-plat. Try Verdana. Also, be sure to allow plenty of room between the tabs for the maximum expansion. Of course, since you have spaces in your heading line (bad boy), then you'll have to go back into that calc line and add padded spaces again to adjust it; whereas, if you would have kept tabs in the heading line, it would adjust when you change the detail.

One of the problems with nifty displays of columnar text fields (such as audit solutions) is that, if a line is longer, it wonks the entire line. You might turn the report to landscape so you have room to expand it even more. In addition to font-display issues, you will have printer issues (each printer requires a different buffer size on the borders) - called unprintable area. Always design your reports with larger-than-needed margins.

As for relational; sure. I'll add it on my (overflowing) plate for next few weeks and we'll see if we can't restructure you properly. But just think of what you would have missed ... you are a Tab Master now!

UPDATE: BTW, use tabs even on the lines (Order # and Acct) - no spaces - tabs only through the whole thing. That last line (when I now view your gif) means your tabs are off. And your gif looks landscape but your file wasn't.

2nd UPDATE: Hint on working with tabs if you need to expand them - start with the last tab and increase it - then work your way up the list. If you start at the top, you'll overlap and FM won't let you. Much easier to grab the last tab and expand a BUNCH - even way past what you think you need. Once you get all tabs expanded, then start with the top tabs and begin decreasing. Your display points to the fact that all those prior name lines are too large for the tab allowed - that the REAL tab is less than their length and the shorter name lands on it. After you change each tab, exit and view the results so you know how much movement (and which columns) are adjusting.

LaRetta

Edited March 19, 2007 by Guest

[sharingsunshine]

Hi LaRetta,

I think my new title of Tab Master may be premature.

I went in and changed the font to Verdana I hadn't done anything to the fonts so it was using only the default.

I changed the tab settings very drastically and the 2 blank lines never moved.

Perhaps something is wrong with my calc statement because it seems something is overriding what I am trying to do with the tabs.

Currently, I can't get it to give me a preview copy via my printer. So can you tell me what to look for in the calc. I just inserted spaces between the ' ' thinking that was how I was supposed to adjust the columns.

I am not clear on what you mean by spaces in the heading. I just used the A option on the tool bar and drew a box and then put the text in. Is there another way to do it?

You have done so much already. Don't spend much time on the relational.

Lastly, can you recommend what book I should get to learn FM better?

I took the tutorial and read on the forums and went to databasepros.com and downloaded his scripts and have attempted to find what I can.

Thanks,

Randal